Import XML string into DB

Hot Topic

Date field cannot be null!
SQL Server 2000 SDK Download
Data-Driven Subscription not shown
Load a SSIS package via Web Service: The package failed to load due to error 0xC0011008 "Error loading from XML.WHAT IS THAT?
Get Error when install SQL Server 2005
Simple Question: Testing an Update Script
clr comcatenate aggregate
stored proc call, 'table name' as string, and t-sql statement
AGGREGATE doesn't do MIN/MAX on textual columns
Installing the SQL server 2005 beta tools problem

SQL Server

Can't uninstall or reinstall nonfunctioning sql server
granting select permission
Executing packages from ASP.Net
pls advise me. HELP pls...
Flat File and uneven number of columns
Installing SQL Express SP1 with existing User Instances
Using the same connection in multiple threads -> native exception
connect to sql server express
Having trouble using my component
System.UnauthorizedAccessException: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))

Import XML string into DB

I'm not sure what an "edge" table is.
 
Can you post a sample of the XML, the OPENXML syntax you're currently using, and the output you're trying to achieve
 

--
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
 
 

Hi, I have a string of xml stored in a ntext field of a table in a SQL Server 2000 database. What I have to do is strip out the data stored in the string and store it in a table in the database. I have succeeded so far in using the OpenXML SQL command but that puts it in a edge table where I am struggling to extract the data as the fields that I require are stored as rows in the edge table and I dont know how to pivot the data so as to select them as fields. Whats more, each XML string can contain more than one record of data to insert which complicates things further when trying to identify which row of data in the edge table is relevant to which record I need to insert.

Can anyone help me with an alternative method of importing this data

Thanks in advance

Answer this question

Import XML string into DB

  • Rodrigo Wolburg

    Can someone tell me what an edge table is

    --
    Adam Machanic
    SQL Server MVP
    http://www.datamanipulation.net
    --
     
     
    It would help if you post some sample schema, data and the results. Using edge table may not be a good option. You could just use OPENXML with the necessary row patterns to get the required format that you want. If you need more help on openxml you can also post in the "SQL Server XML" newsgroups since there are people from the XML team there.

  • Mike Harsh

    Hi, I have a string of xml stored in a ntext field of a table in a SQL Server 2000 database. What I have to do is strip out the data stored in the string and store it in a table in the database. I have succeeded so far in using the OpenXML SQL command but that puts it in a edge table where I am struggling to extract the data as the fields that I require are stored as rows in the edge table and I dont know how to pivot the data so as to select them as fields. Whats more, each XML string can contain more than one record of data to insert which complicates things further when trying to identify which row of data in the edge table is relevant to which record I need to insert.

    Can anyone help me with an alternative method of importing this data

    Thanks in advance

  • TTE

    The flags 0, 1, 2 only have an effect if you leave the row patterns away. In that case, they default the column name to either an attribute or element.
     
     
    Ahhh!  Thanks.  I didn't know that you could ever not specify the pattern in the WITH clause... Makes sense now.

    --
    Adam Machanic
    SQL Server MVP
    http://www.datamanipulation.net
    --
     
     

  • JosRyan

    Thanks, Michael!

    --
    Adam Machanic
    SQL Server MVP
    http://www.datamanipulation.net
    --
     
     
    The table format that is being returned by saying select * from OpenXML(...) without the WITH clause.
     
    Best regards
    Michael

  • Jasimons

    It would help if you post some sample schema, data and the results. Using edge table may not be a good option. You could just use OPENXML with the necessary row patterns to get the required format that you want. If you need more help on openxml you can also post in the "SQL Server XML" newsgroups since there are people from the XML team there.

  • Michael Pollard

    Hi guys, thanks for the help. But here is a new challenge:

    try the following XML string:

    <Update xmlns="http://website.com/">
     <inputDs>
      <xs:schema id="1q2e" xmlns="" xmlns:xs="http://website" xmlns:msdata="value">
       <xs:element name="12312" msdata:IsDataSet="true" msdata:Locale="en-ZA">
        <xs:complexType>
         <xs:choice maxOccurs="unbounded">
          <xs:element name="tb_InUpdate">
           <xs:complexType>
            <xs:sequence>
             <xs:element name="memberid" type="xs:int" minOccurs="0" />
             <xs:element name="firmid" type="xs:int" minOccurs="0" />
             <xs:element name="recordid" type="xs:int" minOccurs="0" />
             <xs:element name="Amount" type="xs:decimal" minOccurs="0" />
            </xs:sequence>
           </xs:complexType>
          </xs:element>
         </xs:choice>
        </xs:complexType>
       </xs:element>
      </xs:schema>
      <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
       <MDataset xmlns="">
        <tb_InUpdate diffgr:id="tb_InUpdate" msdata:rowOrder="0" diffgr:hasChanges="inserted">
         <memberid>111111</memberid>
         <firmid>22222</firmid>
         <recordid>33333</recordid>
         <Amount>300</Amount>
        </tb_InUpdate>
        <tb_InUpdate diffgr:id="tb_InUpdate" msdata:rowOrder="1" diffgr:hasChanges="inserted">
         <memberid>123123</memberid>
         <firmid>234</firmid>
         <recordid>2342</recordid>
         <Amount>400</Amount>
        </tb_InUpdate>
       </MDataset>
      </diffgr:diffgram>
     </inputDs>
    </Update>

    This is more like what I will be importing where I will insert the values of memberid, firmid, recordid and amount for each record.

    Any way to tweak the code to import this string, or possible an alternative method

  • Omar Hector 123

    Hi guys, sorry I havent responded to your questions, been out of the office. I am currently using the following code to import the XML string into the edge table:

    declare @idoc int
    declare @doc varchar(8000)
    set @doc = '< xml version="1.0" >
    <Books>
          <Book>
                <Title>XML Application Development with MSXML 4.0</Title>
                <Publisher>Wrox Press</Publisher>
                <DateOfPurchase>2/1/2002</DateOfPurchase>
          </Book>
          <Book>
                <Title>Professional SQL Server 2000 XML</Title>
                <Publisher>Wrox Press</Publisher>
                <DateOfPurchase>9/10/2001</DateOfPurchase>
          </Book>
    </Books>'
    --Create an internal representation of the XML document.
    exec sp_xml_preparedocument @idoc OUTPUT, @doc
    -- SELECT statement using OPENXML rowset provider
    SELECT    * into #xml
    FROM       OPENXML (@idoc, '/')
    EXEC sp_xml_removedocument @idoc
    select * from #xml

    What I need to get now is the data from the xml string i.e. the title, publisher and date of purchase and insert it into a table. Notice there 2 records in this particualr string. Make sense to you guys


  • Chango V.

    If you know how your XML maps to rows and columns, you should look at the OpenXML WITH clause.
     
    Also, if you have SQL Server 2005, please check out the XML datatype and the nodes() method.
     
    Best regards
    Michael
    I'm not sure what an "edge" table is.
     
    Can you post a sample of the XML, the OPENXML syntax you're currently using, and the output you're trying to achieve
     

    --
    Adam Machanic
    SQL Server MVP
    http://www.datamanipulation.net
    --
     
     

    Hi, I have a string of xml stored in a ntext field of a table in a SQL Server 2000 database. What I have to do is strip out the data stored in the string and store it in a table in the database. I have succeeded so far in using the OpenXML SQL command but that puts it in a edge table where I am struggling to extract the data as the fields that I require are stored as rows in the edge table and I dont know how to pivot the data so as to select them as fields. Whats more, each XML string can contain more than one record of data to insert which complicates things further when trying to identify which row of data in the edge table is relevant to which record I need to insert.

    Can anyone help me with an alternative method of importing this data

    Thanks in advance

  • Ed Psyk

    Hi Adam
     
    Thanks for helping out. One small nit addendum: If you know exactly how your path looks like, please try to avoid wildcards and // in your query. So in the case below, please write /Books/Book instead of /Books/*, unless you want to be able to pick up non-Book elements as well...
     
    Best regards
    Michael

    Hi guys, sorry I havent responded to your questions, been out of the office. I am currently using the following code to import the XML string into the edge table:

    What I need to get now is the data from the xml string i.e. the title, publisher and date of purchase and insert it into a table. Notice there 2 records in this particualr string. Make sense to you guys

     
    Sure...
     
    declare @idoc int
    declare @doc varchar(8000)
    set @doc = '< xml version="1.0" >
    <Books>
          <Book>
                <Title>XML Application Development with MSXML 4.0</Title>
                <Publisher>Wrox Press</Publisher>
                <DateOfPurchase>2/1/2002</DateOfPurchase>
          </Book>
          <Book>
                <Title>Professional SQL Server 2000 XML</Title>
                <Publisher>Wrox Press</Publisher>
                <DateOfPurchase>9/10/2001</DateOfPurchase>
          </Book>
    </Books>'
    --Create an internal representation of the XML document.
    exec sp_xml_preparedocument @idoc OUTPUT, @doc
    -- SELECT statement using OPENXML rowset provider
    SELECT    *
    FROM       OPENXML (@idoc, '/Books/*')
    WITH
    (
     Title VARCHAR(250) 'Title',
     Publisher VARCHAR(100) 'Publisher',
     DateOfPurchase SMALLDATETIME 'DateOfPurchase'
    )
    EXEC sp_xml_removedocument @idoc
     

    --
    Adam Machanic
    SQL Server MVP
    http://www.datamanipulation.net
    --
     

  • fuzzfoot

    The table format that is being returned by saying select * from OpenXML(...) without the WITH clause.
     
    Best regards
    Michael
    Can someone tell me what an edge table is

    --
    Adam Machanic
    SQL Server MVP
    http://www.datamanipulation.net
    --
     
     
    It would help if you post some sample schema, data and the results. Using edge table may not be a good option. You could just use OPENXML with the necessary row patterns to get the required format that you want. If you need more help on openxml you can also post in the "SQL Server XML" newsgroups since there are people from the XML team there.

  • rumen101010

    Hi guys, sorry I havent responded to your questions, been out of the office. I am currently using the following code to import the XML string into the edge table:

    What I need to get now is the data from the xml string i.e. the title, publisher and date of purchase and insert it into a table. Notice there 2 records in this particualr string. Make sense to you guys

     
    Sure...
     
    declare @idoc int
    declare @doc varchar(8000)
    set @doc = '< xml version="1.0" >
    <Books>
          <Book>
                <Title>XML Application Development with MSXML 4.0</Title>
                <Publisher>Wrox Press</Publisher>
                <DateOfPurchase>2/1/2002</DateOfPurchase>
          </Book>
          <Book>
                <Title>Professional SQL Server 2000 XML</Title>
                <Publisher>Wrox Press</Publisher>
                <DateOfPurchase>9/10/2001</DateOfPurchase>
          </Book>
    </Books>'
    --Create an internal representation of the XML document.
    exec sp_xml_preparedocument @idoc OUTPUT, @doc
    -- SELECT statement using OPENXML rowset provider
    SELECT    *
    FROM       OPENXML (@idoc, '/Books/*')
    WITH
    (
     Title VARCHAR(250) 'Title',
     Publisher VARCHAR(100) 'Publisher',
     DateOfPurchase SMALLDATETIME 'DateOfPurchase'
    )
    EXEC sp_xml_removedocument @idoc
     

    --
    Adam Machanic
    SQL Server MVP
    http://www.datamanipulation.net
    --
     

  • furtivefelon

     
     
    Thanks for helping out. One small nit addendum: If you know exactly how your path looks like, please try to avoid wildcards and // in your query. So in the case below, please write /Books/Book instead of /Books/*, unless you want to be able to pick up non-Book elements as well...
     
     
    I'll keep that in mind.
     
    By the way, can you show an example of how the flags byte affects the output   I've never seen any change when modifying it.
     

    --
    Adam Machanic
    SQL Server MVP
    http://www.datamanipulation.net
    --

  • David McComb

    The flags 0, 1, 2 only have an effect if you leave the row patterns away. In that case, they default the column name to either an attribute or element.
     
    So
     
    SELECT    *
    FROM       OPENXML (@idoc, '/Books/*', 2)
    WITH
    (
     Title VARCHAR(250) ,
     Publisher VARCHAR(100) ,
     DateOfPurchase SMALLDATETIME
    )
    is equivalent to
     
    SELECT    *
    FROM       OPENXML (@idoc, '/Books/*')
    WITH
    (
     Title VARCHAR(250) 'Title',
     Publisher VARCHAR(100) 'Publisher',
     DateOfPurchase SMALLDATETIME 'DateOfPurchase'
    )
    while
     
    SELECT    *
    FROM       OPENXML (@idoc, '/Books/*', 1)
    WITH
    (
     Title VARCHAR(250),
     Publisher VARCHAR(100) ,
     DateOfPurchase SMALLDATETIME
    )
    is equivalent to
     
    SELECT    *
    FROM       OPENXML (@idoc, '/Books/*')
    WITH
    (
     Title VARCHAR(250) '@Title',
     Publisher VARCHAR(100) '@Publisher',
     DateOfPurchase SMALLDATETIME '@DateOfPurchase'
    )
    Also, the flag value 3 will default to first attributes and then elements if no attribute value is there.
     
    Adding 8 to any of the value will impact the mapping to @mp:xmltext.
     
    Best regards
    Michael
     
     
     
    Thanks for helping out. One small nit addendum: If you know exactly how your path looks like, please try to avoid wildcards and // in your query. So in the case below, please write /Books/Book instead of /Books/*, unless you want to be able to pick up non-Book elements as well...
     
     
    I'll keep that in mind.
     
    By the way, can you show an example of how the flags byte affects the output   I've never seen any change when modifying it.
     

    --
    Adam Machanic
    SQL Server MVP
    http://www.datamanipulation.net
    --
  • Import XML string into DB
Answer this question