none
Can I insert XML data into a table with "INSERT INTO" command directly? RRS feed

  • Question

  • Hi,

    Just wonder for a very very simple XML file, like below:

    <?xml version="1.0" encoding="utf-8" ?>

    <sample1>
    <company>Chilkat Software Inc</company>
    <city>Wheaton</city>
    <year-founded>1998</year-founded>
    <industry>software</industry>
    </sample1>

    Can I use "INSERT INTO" command to insert it into a table with XML type column directly, like below:

    INSERT INTO [XMLTable] Values('<?xml version="1.0" encoding="utf-8" ?>

    <sample1>
    <company>Chilkat Software Inc</company>
    <city>Wheaton</city>
    <year-founded>1998</year-founded>
    <industry>software</industry>
    </sample1>')

    I try to do so but get many errors. Why?

    Thanks

    Sunday, June 30, 2019 3:27 AM

Answers

  • When you ask a question never say that you "got (many) errors". Include the error message, so that we can tell what is going on.

    The above looks good to me, and I was able to run the below successfully:

    DECLARE @x TABLE (x xml)
    INSERT @x VALUES ('<?xml version="1.0" encoding="utf-8" ?>
    <sample1>
    <company>Chilkat Software Inc</company>
    <city>Wheaton</city>
    <year-founded>1998</year-founded>
    <industry>software</industry>
    </sample1>')
    


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by tempc Tuesday, July 9, 2019 11:54 PM
    Sunday, June 30, 2019 9:28 AM
  • Hi tempc,

    Here is what you need, load XML file into a DB table as is:

    DECLARE @tbl TABLE (ID int IDENTITY(1,1),XmlColumn xml);
    
    INSERT INTO @tbl (XmlColumn)  
    SELECT * FROM OPENROWSET(BULK N'e:\Temp\books.xml', SINGLE_BLOB) AS x;
    
    SELECT * FROM @tbl;

    • Marked as answer by tempc Tuesday, July 9, 2019 11:54 PM
    Sunday, June 30, 2019 5:07 PM

All replies

  • Are you looking for this?

    Declare @x as xml = '<?xml version="1.0" encoding="utf-8" ?>
    <sample1>
    <company>Chilkat Software Inc</company>
    <city>Wheaton</city>
    <year-founded>1998</year-founded>
    <industry>software</industry>
    </sample1>'
    SELECT  
           Tbl.Col.value('company[1]', 'varchar(max)'),
    	   Tbl.Col.value('city[1]', 'varchar(max)'),
    	   Tbl.Col.value('year-founded[1]', 'int'),
    	   Tbl.Col.value('industry[1]', 'varchar(max)')
     FROM   @x.nodes('//sample1') Tbl(Col) 


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
     [Blog]  [LinkedIn]

    Sunday, June 30, 2019 6:00 AM
  • Thank you.

    I do not want to extract the node contents from the XML file, I just want to insert the XML data into the table with the simplest way.

    Sunday, June 30, 2019 7:38 AM
  • When you ask a question never say that you "got (many) errors". Include the error message, so that we can tell what is going on.

    The above looks good to me, and I was able to run the below successfully:

    DECLARE @x TABLE (x xml)
    INSERT @x VALUES ('<?xml version="1.0" encoding="utf-8" ?>
    <sample1>
    <company>Chilkat Software Inc</company>
    <city>Wheaton</city>
    <year-founded>1998</year-founded>
    <industry>software</industry>
    </sample1>')
    


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by tempc Tuesday, July 9, 2019 11:54 PM
    Sunday, June 30, 2019 9:28 AM
  • Hi tempc,

    Here is what you need, load XML file into a DB table as is:

    DECLARE @tbl TABLE (ID int IDENTITY(1,1),XmlColumn xml);
    
    INSERT INTO @tbl (XmlColumn)  
    SELECT * FROM OPENROWSET(BULK N'e:\Temp\books.xml', SINGLE_BLOB) AS x;
    
    SELECT * FROM @tbl;

    • Marked as answer by tempc Tuesday, July 9, 2019 11:54 PM
    Sunday, June 30, 2019 5:07 PM
  • I have an XML file with xmlns below is the formate in future the nodes may increase or decrease but I want to load each node to sql table dynamically please help me on this I have this xml value in sql table alog with another column.

    <Parts xmlns="http://www.oracle.com/webfolder/technetwork/xml/plm/2015/09/" uniqueId="XXXXXXXXXXX">
      <TitleBlock>
        <Number>ABS-11111</Number>
        <PartType>Finished Good</PartType>
        <LifecyclePhase>Obsolete</LifecyclePhase>
        <Description>MIXING AND DELIVERY SYSTEM, 3CC</Description>
        <PartCategory>INC</PartCategory>
        <Rev>13        ECO-12225</Rev>
        <RevIncorpDate>2017-11-07T14:05:52Z</RevIncorpDate>
        <RevReleaseDate>2017-11-09T22:28:37Z</RevReleaseDate>
      </TitleBlock>
      <PageThree>
        <List10>600 - Biologics</List10>
        <List24>Blood and Bone Instruments</List24>
        <List03>Standard</List03>
        <Text02>ABS-1000</Text02>
        <List25>60</List25>
        <List23>2</List23>
        <List18>NA</List18>
        <List19>No</List19>
        <List15>No</List15>
        <List14>No</List14>
        <List21>No</List21>
        <List13>Yes</List13>
        <List05>No</List05>
        <List17>No</List17>
        <List16>Yes</List16>
        <List12>No</List12>
        <List04>Yes</List04>
        <List09>No</List09>
        <List01>Yes</List01>
        <List08>No</List08>
        <List06>Yes</List06>
        <List11>No</List11>
        <List07>00888867001800</List07>
        <Text03>00888867001800</Text03>
        <Numeric01>1</Numeric01>
        <Multilist03>
          <Value>EMEA</Value>
          <Value>LATAM</Value>
          <Value>US/CAN</Value>
        </Multilist03>
        <FlexAttributes>
          <FlexAttribute Id="2476273">
            <name>MPI#</name>
            <value xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsd:type="MultiListType">
              <Value>MPI-1438</Value>
            </value>
          </FlexAttribute>
          <FlexAttribute Id="2473989">
            <name>Sterilization Processing Group</name>
            <value xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsd:type="MultiListType">
              <Value>Steritec Cycle #4</Value>
              <Value>Tri-State Centurion PMP-1 (T/S 20)</Value>
            </value>
          </FlexAttribute>
          <FlexAttribute Id="2473990">
            <name>Sterilization Product Family</name>
            <value xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsd:type="MultiListType">
              <Value>INC EO 1</Value>
              <Value>INC EO 2</Value>
            </value>
          </FlexAttribute>
          <FlexAttribute Id="2476994">
            <name>Shipped Sterilization Method</name>
            <value xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsd:type="xsd:string">EO</value>
          </FlexAttribute>
        </FlexAttributes>
      </PageThree>
      <PageTwo>
        <List02>ABS-1000</List02>
        <List01>EA</List01>
        <CreateUser>Migration Admin, Data</CreateUser>
        <Multilist01>
          <Value>Coastal Life, ( 104728 )</Value>
          <Value>Phoenix Medical, ( 100007 )</Value>
        </Multilist01>
        <Text03>ABS1000</Text03>
      </PageTwo>
    </Parts>

    Monday, July 15, 2019 6:44 AM
  • Hi DeviRama,

    Please open a separate thread for your issue.

    Also, you need to provide a destination table structure and explain how to map XML elements and that table columns.

    Monday, July 15, 2019 6:53 AM