rory83 rory83 - 4 months ago 13
SQL Question

Import XML to SQL table and format data

Would someone be able to help me I am trying to import an xml file to a sql server table. I can import the required data but having some trouble getting it in the format required.

declare @input XML = '<Sub>
<Results>
<Result>
<ids>
<id>
<type>code</type>
<value>9004a3d2</value>
</id>
<id>
<type>username</type>
<value>jbloggs001</value>
<date>20160725</date>
</id>
<id>
<type>EmployeeID</type>
<value>01234</value>
<date>20160725</date>
</id>
</ids>
</Result>
</Results>
</Sub>'
SELECT
datatype = XCol.value('(type)[1]','varchar(25)'),
datavalue = XCol.value('(value)[1]','varchar(50)')
FROM
@input.nodes('/Sub/Results/Result/ids/id') AS XTbl(XCol)


This gives 3 columns like:

datatype datavalue
--------------------------------
code 9004a3d2
username jbloggs001
employeeID 01234


Would it be possible to get it to import as?

EmployeeID USername Code
---------------------------------
01234 jbloggs 0019004a3d2


Thanks

Answer

As was suggested in comments that could be done using PIVOT:

   SELECT *
   FROM (
   SELECT
     datatype = XCol.value('(type)[1]','varchar(25)'),
    datavalue = XCol.value('(value)[1]','varchar(50)')
   FROM 
      @input.nodes('/Sub/Results/Result/ids/id') AS XTbl(XCol)
    ) as p
    PIVOT (
        MAX(datavalue) FOR datatype IN (EmployeeID,username,code)
    ) as pvt

Output:

EmployeeID  username    code
01234       jbloggs001  9004a3d2

If input is always with same types:

SELECT  XCol.value('(id/value)[3]','varchar(50)') as EmployeeID,
        XCol.value('(id/value)[2]','varchar(50)') as username,
        XCol.value('(id/value)[1]','varchar(50)') as code
FROM @input.nodes('/Sub/Results/Result/ids') AS XTbl(XCol)
Comments