You must Sign In to post a response.
  • Category: SQL Server

    Convert a XMl Into Table

    Dear All,


    --create table tmp_DV(sno int,Name varchar(20))
    /*
    exec dp_convertxml @strXML=N'
    <NewDataSet>
    <EmpDetails>
    <sno>11</sno>
    <Name>Haja</Name>
    </EmpDetails>
    </NewDataSet>'
    */
    Alter Procedure dp_convertxml
    (
    @strXML xml
    )
    as
    Begin


    Declare @intPointer int
    exec sp_xml_preparedocument @intPointer output, @strXML

    --Insert into tmp_DV(sno,Name)
    Select sno,Name
    from OpenXml(@intPointer,'NewDataset/EmpDetails',2)
    With (sno int 'sno',Name varchar(20) 'Name')



    exec sp_xml_removedocument @intPointer

    select * from tmp_DV
    End

    I attached my Sp with Table creation. While converting the xml into table. it doesnt show any error, but no can be inserting. Kindly guide me.
  • #766498
    Hi,

    I think the problem is with the 3rd parameter in the OpenXml, Please ignore it and try. since it is a optional parameter, it doesn't cause any issue.
    <pre>Select sno,Name from OpenXml(@intPointer,'NewDataset/EmpDetails')
    With (sno int 'sno',Name varchar(20) 'Name')</pre>

    Hope it helps
    Thank you

  • #766506
    This link will give an apt solution to your problem can you check once.
    http://www.dotnetspider.com/resources/45904-Error-ORA-01460-unimplemented-or-unreasonable-conversion-requested.aspx

    SRI RAMA PHANI BHUSHAN KAMBHAMPATI

  • #766507

    CREATE OR REPLACE PROCEDURE SP_Ins_(s_partDetails VARCHAR2) AS
    XMLType SYS.XMLTYPE;


    /******************************************************************************
    NAME: SP_Ins_Soverignpartinfo
    PURPOSE:

    REVISIONS:
    Ver Date Author Description
    --------- ---------- --------------- ------------------------------------
    1.0 6/2/2014 1. Created this procedure.

    NOTES:

    Automatically available Auto Replace Keywords:
    Object Name: SP_Ins_Soverignpartinfo
    Sysdate: 6/2/2014
    Date and Time: 6/2/2014, 3:40:49 PM, and 6/2/2014 3:40:49 PM
    Username: (set in TOAD Options, Procedure Editor)
    Table Name: (set in the "New PL/SQL Object" dialog)

    ******************************************************************************/

    BEGIN
    XMLType := sys.xmltype.CreateXML(s_partDetails);

    FOR partinfo in
    (
    select
    --Soverignpartinfo.Extract('//DATASET TABLE NAME/MAXAVAILABILITYQTY/text()').getstringval() as maxavailibilitys,
    Soverignpartinfo.Extract('//DATASET TABLE NAME/PARTNO/text()').getstringval() as partno,
    Soverignpartinfo.Extract('//DATASET TABLE NAME/PRICE/text()').getstringval() as price,
    Soverignpartinfo.Extract('//DATASET TABLE NAME/CURRENCY/text()').getstringval() as currency,
    Soverignpartinfo.Extract('//DATASET TABLE NAME/MAXAVAILABILITYQTY/text()').getstringval() as maxavailibilitys,
    Soverignpartinfo.Extract('//DATASET TABLE NAME/MINQTY/text()').getstringval() as Minqty,
    Soverignpartinfo.Extract('//DATASET TABLE NAME/AnonymousPrice/text()').getstringval() as AnonymousPrice
    FROM
    TABLE (XMLSEQUENCE (XMLTYPE.EXTRACT('//NewDataSet/SoverignPartInfo')))Soverignpartinfo
    )
    loop

    DECLARE
    Countint NUMBER;
    BEGIN
    SELECT Count(*) INTO Countint FROM SOVERIGN_PARTINFO
    WHERE PARTNO = TRIM(partinfo.PARTNO); -- no such number

    IF Countint = 0 then


    INSERT INTO SOVERIGN_PARTINFO(PARTNO,PRICE,CURRENCY,MAXAVAILABILITYQTY,LASTUPDATEDDATE,MINQTY,AnonymousPrice) VALUES
    (TRIM(partinfo.PARTNO),TRIM(partinfo.price),TRIM(partinfo.currency),TRIM(partinfo.maxavailibilitys),sysdate,trim(partinfo.Minqty),trim(partinfo.AnonymousPrice));

    else

    UPDATE SOVERIGN_PARTINFO SET PRICE = trim(partinfo.price),CURRENCY=trim(partinfo.currency),MAXAVAILABILITYQTY= trim(partinfo.maxavailibilitys),LASTUPDATEDDATE=sysdate,MINQTY= trim(partinfo.Minqty),AnonymousPrice= trim(partinfo.AnonymousPrice) WHERE trim(PARTNO) = trim(partinfo.PARTNO);

    END IF;
    END;
    End LOOP;
    COMMIT;
    END SP_Ins_Soverignpartinfo;


    SRI RAMA PHANI BHUSHAN KAMBHAMPATI

  • #766572
    There are couple of ways to convert XML in to table
    1. read XML In dataset, it has inbuilt readXML method that can read xml and convert it to dataset
    2. read xml using xmlreader, xpathnavigator, XMLDocument and create a datatable object
    then you can insert data in it

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #766575
    We have many ways to do it. All are depend on your requirement.
    1. If you want to save the xml as a string. You can create table and create column of text for the XML string. you can directly call the insert command and save the string.
    2. You can use the Serialization and Deserialization concept. You can create Object based on your xml. You can convert Serialize/Deserialize based on your requirement. The you can save the object properties in your table DB
    3. Create DataSet and read the XML using readXML and update it.
    4. You can pars the XML directly and get the values using XPath query, then you can store it in your DB

    All are depending on your requirement so you can do any of the above ways.

    By Nathan
    Direction is important than speed


Sign In to post your comments