none
TSQL - To Load specific columns of an XML file in to a table

    Question

  • I need to load 2 columns (say name and address) from an XML file in to a table. This XML file is placed in a folder and I need to write T-SQL to pull the data and load these two columns in to the specified table. Any help would be greatly appreciated.
    Wednesday, September 26, 2012 7:51 PM

Answers

  • I have just written the code and not tested as i dont have sql server at home..

    DECLARE @xml xml
    
    SELECT
    
    @xml=CAST(BulkColumn as xml) FROM OPENROWSET(BULK N‘C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Package Samples\ProcessXMLData Sample\DataFiles\Orders.xml’, SINGLE_BLOB) A
    
    DECLARE @Temp TABLE(Name VARCHAR(10), Address varchar(5))  
    
    DECLARE @docHandle int
    EXEC sp_xml_preparedocument @docHandle OUTPUT, @doc
    
    INSERT INTO @Temp(Name,Address)
         SELECT t.value('./Data[1]','VARCHAR(10)') AS 'Name',t.value('./Data[2]','VARCHAR(50)') AS 'Address',
           FROM OPENXML(@docHandle, '//Data', 1) t
    
    EXEC sp_xml_removedocument @docHandle
    
    

    You need to change the file location and XML nodes based on your XML structure.

    Let me know if it works.


    Best Regards, Venkat

    • Proposed as answer by S_Surve Thursday, September 27, 2012 3:05 AM
    • Marked as answer by ABCD0008 Thursday, September 27, 2012 12:56 PM
    Wednesday, September 26, 2012 8:01 PM
  • Can you post some sample XML?  Also what version of SQL Server are you using?

    If you're using SQL2005 or above, you can use the XML datatype and its methods ( eg .value, .nodes, .query ).  Try this example:

    /*  If you had some XML which looked like this for example
    <customers>
    	<customer>
    		<name>wBob</name>
    		<address>1 XML Avenue</address>
    	</customer>
    	<customer>
    		<name>abcd</name>
    		<address>8 TSQL Road</address>
    	</customer>
    </customers>
    */
    
    DECLARE @xml XML
    
    SELECT @xml = x.y
    FROM OPENROWSET( BULK 'c:\temp\temp.xml', SINGLE_CLOB ) x(y)
    
    -- INSERT INTO ...
    SELECT
    	c.c.value('(name/text())[1]', 'varchar(50)') name,
    	c.c.value('(address/text())[1]', 'varchar(50)') address
    FROM @xml.nodes('customers/customer') c(c)

    • Proposed as answer by S_Surve Thursday, September 27, 2012 3:05 AM
    • Marked as answer by Naomi NModerator Friday, September 28, 2012 3:25 AM
    Wednesday, September 26, 2012 9:12 PM

All replies

  • I have just written the code and not tested as i dont have sql server at home..

    DECLARE @xml xml
    
    SELECT
    
    @xml=CAST(BulkColumn as xml) FROM OPENROWSET(BULK N‘C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Package Samples\ProcessXMLData Sample\DataFiles\Orders.xml’, SINGLE_BLOB) A
    
    DECLARE @Temp TABLE(Name VARCHAR(10), Address varchar(5))  
    
    DECLARE @docHandle int
    EXEC sp_xml_preparedocument @docHandle OUTPUT, @doc
    
    INSERT INTO @Temp(Name,Address)
         SELECT t.value('./Data[1]','VARCHAR(10)') AS 'Name',t.value('./Data[2]','VARCHAR(50)') AS 'Address',
           FROM OPENXML(@docHandle, '//Data', 1) t
    
    EXEC sp_xml_removedocument @docHandle
    
    

    You need to change the file location and XML nodes based on your XML structure.

    Let me know if it works.


    Best Regards, Venkat

    • Proposed as answer by S_Surve Thursday, September 27, 2012 3:05 AM
    • Marked as answer by ABCD0008 Thursday, September 27, 2012 12:56 PM
    Wednesday, September 26, 2012 8:01 PM
  • You could use SSIS to achieve this or see the following link (SQL Server Solution) to achieve using T-SQL

    http://riteshkk2000.blogspot.com/2012/01/loadimport-xml-file-into-database-table.html


    Narsimha

    Wednesday, September 26, 2012 8:10 PM
  • Thanks Venkat. I will let you know how it goes.
    Wednesday, September 26, 2012 8:45 PM
  • Thanks narasimha. I have to use T-SQL here not the ssis
    Wednesday, September 26, 2012 8:45 PM
  • Can you post some sample XML?  Also what version of SQL Server are you using?

    If you're using SQL2005 or above, you can use the XML datatype and its methods ( eg .value, .nodes, .query ).  Try this example:

    /*  If you had some XML which looked like this for example
    <customers>
    	<customer>
    		<name>wBob</name>
    		<address>1 XML Avenue</address>
    	</customer>
    	<customer>
    		<name>abcd</name>
    		<address>8 TSQL Road</address>
    	</customer>
    </customers>
    */
    
    DECLARE @xml XML
    
    SELECT @xml = x.y
    FROM OPENROWSET( BULK 'c:\temp\temp.xml', SINGLE_CLOB ) x(y)
    
    -- INSERT INTO ...
    SELECT
    	c.c.value('(name/text())[1]', 'varchar(50)') name,
    	c.c.value('(address/text())[1]', 'varchar(50)') address
    FROM @xml.nodes('customers/customer') c(c)

    • Proposed as answer by S_Surve Thursday, September 27, 2012 3:05 AM
    • Marked as answer by Naomi NModerator Friday, September 28, 2012 3:25 AM
    Wednesday, September 26, 2012 9:12 PM