Import XML file to SQL Server Horizontally

Question

I am trying to import XML data into SQL server. I found below link.

My requirement is slight different, where I need to create the table with <name> as column name and <value> as column values. like below

XML:

<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="urn:toatech:ResourceManagement:1.0">
   <SOAP-ENV:Body>
      <ns1:get_resources_list_response>
         <result_code>0</result_code>
         <resources>
            <resource>
               <properties>
                  <property>
                     <name>id</name>
                     <value>CAP_LOAD_TEST</value>
                  </property>
                  <property>
                     <name>status</name>
                     <value>inactive</value>
                  </property>
                  <property>
                     <name>parent_id</name>
                     <value>ABC_CO</value>
                  </property>
                  <property>
                     <name>type</name>
                     <value>GR</value>
                  </property>
                  <property>
                     <name>name</name>
                     <value>Capacity LoadTesting</value>
.
.
.
.

Code:

DECLARE @XmlFile XML

SELECT @XmlFile = BulkColumn  
FROM OPENROWSET(BULK 'C:\Users\GET_RESOURCE_LIST.xml', SINGLE_BLOB) x

SELECT 
    Resource_id = resource.value('(value)[1]', 'varchar(255)'),
    Resource_status = resource.value('(value)[2]', 'varchar(255)')
 FROM
@XmlFile.nodes('//resources/resource/properties/property') AS XTbl1(resource)

So when "resource" item repeats it should be a new row in the table with new set of values

Expected Table Output:

    id              status      parent_id   type    name
    ==              ======      =========   ====    ====
    CAP_LOAD_TEST   inactive    ABC_CO      GR      Capacity LoadTesting

...

With the above code all the values populating to one column. That was the issue.

Backs · Accepted Answer · 2015-08-26T08:38:46.503

You need PIVOT operation with dynamic query:

DECLARE @xml XML = N'<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="urn:toatech:ResourceManagement:1.0">
   <SOAP-ENV:Body>
      <ns1:get_resources_list_response>
         <result_code>0</result_code>
         <resources>
            <resource>
               <properties>
                  <property>
                     <name>id</name>
                     <value>CAP_LOAD_TEST</value>
                  </property>
                  <property>
                     <name>status</name>
                     <value>inactive</value>
                  </property>
                  <property>
                     <name>parent_id</name>
                     <value>ABC_CO</value>
                  </property>
                  <property>
                     <name>type</name>
                     <value>GR</value>
                  </property>
                  <property>
                     <name>name</name>
                     <value>Capacity LoadTesting</value>
                  </property>
                </properties>
             </resource>
             </resources>
    </ns1:get_resources_list_response>
            </SOAP-ENV:Body>
</SOAP-ENV:Envelope>'

DECLARE @Output nvarchar(max) = N''
DECLARE @PivotList nvarchar(max)


SELECT 
    @PivotList = COALESCE(@PivotList + ', ', N'') + N'[' + XC.value('./name[1]', 'varchar(255)') + N']'
FROM 
    @xml.nodes('//resources/resource[1]/properties/property') AS XT(XC)

SET @Output = N'SELECT 
    '+@PivotList+N'
FROM
(
    SELECT 
        Resource_id = resource.value(''(name)[1]'', ''varchar(255)''),
        Resource_status = resource.value(''(value)[1]'', ''varchar(255)''),
        unique_b_node = resource.value(''for $i in . return count(../../../*[. << $i]) + 1'', ''int'')
     FROM
    @xml.nodes(''//resources/resource/properties/property'') AS XTbl1(resource)
) AS s
PIVOT
(
    MAX(Resource_status)
    FOR Resource_id IN ('+@PivotList+N')
) AS t;'

EXEC sp_executesql @Output, N'@xml xml', @xml = @xml;

Thanks for your reply. Here when ` ... ` elements repeat, I get error `The column 'id' was specified multiple times for 't'. ` Do we need to handle that separately? — shabar, Aug 26 '15 at 06:27
@shabar do you know list of columns you want to get? or its dynamic? — Backs, Aug 26 '15 at 08:04
Yes, currently its fixed. It will be great if could support dynamic situation as well — shabar, Aug 26 '15 at 08:10

score 1 · Answer 2 · answered Aug 26 '15 at 10:23

Try the below code snippet, this should work if you provide multiple <resource> ... </resource> nodes. I have hard coded 5 below depending on the number of distinct property name. That can be taken care too by finding the count of distinct names of Resource_id from the temp table.

DECLARE @XmlFile XML = '<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="urn:toatech:ResourceManagement:1.0">
   <SOAP-ENV:Body>
      <ns1:get_resources_list_response>
         <result_code>0</result_code>
         <resources>
            <resource>
               <properties>
                  <property>
                     <name>id</name>
                     <value>CAP_LOAD_TEST1</value>
                  </property>
                  <property>
                     <name>status</name>
                     <value>inactive1</value>
                  </property>
                  <property>
                     <name>parent_id</name>
                     <value>ABC_CO1</value>
                  </property>
                  <property>
                     <name>type</name>
                     <value>GR1</value>
                  </property>
                  <property>
                     <name>name</name>
                     <value>Capacity LoadTesting1</value>
                  </property>
                </properties>
            </resource>
         </resources>
              <resources>
            <resource>
               <properties>
                  <property>
                     <name>id</name>
                     <value>CAP_LOAD_TEST2</value>
                  </property>
                  <property>
                     <name>status</name>
                     <value>inactive2</value>
                  </property>
                  <property>
                     <name>parent_id</name>
                     <value>ABC_CO2</value>
                  </property>
                  <property>
                     <name>type</name>
                     <value>GR2</value>
                  </property>
                  <property>
                     <name>name</name>
                     <value>Capacity LoadTesting2</value>
                  </property>
                </properties>
            </resource>
         </resources>
      </ns1:get_resources_list_response>
   </SOAP-ENV:Body>
</SOAP-ENV:Envelope>'
CREATE TABLE #tab (groupID INT IDENTITY(1,1), Resource_id NVARCHAR(50),Resource_status VARCHAR(255))
INSERT INTO #tab
SELECT 
    Resource_id = resource.value('(name)[1]', 'nvarchar(50)'),
    Resource_status = resource.value('(value)[1]', 'varchar(255)')
FROM
@XmlFile.nodes('//resources/resource/properties/property') AS XTbl1(resource)

;WITH cte AS(SELECT Resource_id,Resource_status,(groupID-1)/5 groupID FROM #tab)
SELECT id,status,parent_id,type,name
FROM cte
PIVOT (MAX(Resource_status) FOR Resource_ID IN ( id,status,parent_id,type,name)) A

Import XML file to SQL Server Horizontally

2 Answers2