[SOLVED] Shred XML file into SQL Server table

Shred XML file into SQL Server table

I have researched the best way to shred this xml file extensively and have come close but not all the way to what I want.

I am using SQL Server 2012 and have Visual Studio 2012 as well though I prefer to use SQL Server.

Here is a snippet of the type of XML data I am working with. I cannot control how the XML is built as it comes from a 3rd party. In Reality below the node there are about 450 response types such as ResponseID, Name, Status etc... I only show about ten.

<xml>
    <Response>
        <ResponseID>R_a4yThVvKXzVyftz</ResponseID>
        <ResponseSet>Default Response Set</ResponseSet>
        <Name>Doe, John</Name>
        <ExternalDataReference>0</ExternalDataReference>
        <EmailAddress>jdoe@gmail.com</EmailAddress>
        <IPAddress>140.123.12.123</IPAddress>
        <Status>0</Status>
        <StartDate>2014-09-18 09:21:11</StartDate>
        <EndDate>2014-09-23 16:09:58</EndDate>
        <Finished>1</Finished>
            </Response>
</xml>

I've tried the OPENROWSET Method shown on this site

http://blogs.msdn.com/b/simonince/archive/2009/04/24/flattening-xml-data-in-sql-server.aspx

Using a query like this:

SELECT
a1.value('(RESPONSEID/text())[1]', 'varchar(50)') as RESPONSEID,
a2.value('(RESPONSESET/text())[1]', 'varchar(50)') as RESPONSESET,
a3.value('(NAME/text())[1]', 'varchar(50)') as NAME
FROM  XmlSourceTable
CROSS APPLY XmlData.nodes('//Response') AS RESPONSEID(a1)
CROSS APPLY XmlData.nodes('//Response') AS RESPONSESET(a2)
CROSS APPLY XmlData.nodes('//Response') AS NAME(a3)

I got this to work once, but the shredded output was repeating values and not appearing in the table form I want which is like output below, though note in reality the table is very wide, at least 450 rows in all. Another issue is due to the width being greater than 255 I can't convert this to .txt and import it though I'd strongly prefer to consume and shred the native XML so this process can be automated:

RESPONSEID  RESPONSESET NAME    EXTERNALDATAREFERENCE   EMAILADDRESS    IPADDRESS   STATUS  STARTDATE   ENDDATE
R_a4yThVvKXzVyftz   Default Response Set    Doe, John   1/1/2014    doej@gmail.com  123.12.123  0   9/18/2014 9:21  9/23/2014 16:09
R_06znwEis73yLsnX   NonDefault Response Set Doe, Jane   1/1/2014    doeja@gmail.com 123.12.123  0   9/18/2014 5:29  9/29/2014 9:42
R_50HuB0jDFfI6hmZ   Response Set 1  Doe, Cindy  1/1/2014    doec@gmail.com  123.12.123  0   9/18/2014 17:21 10/1/2014 11:45

I did find this application

https://www.novixys.com/ExultSQLServer/

to shred XML files which created a single table for the Nodehowever in addition to the response table it creates a table for each and every response node which results in about 500 additional tables. Also the application costs $250..

https://www.novixys.com/ExultSQLServer/

Solution

You don't need to add a cross apply for each value you want to extract. One is enough.

SELECT
  R.X.value('(ResponseID/text())[1]', 'varchar(50)') as RESPONSEID,
  R.X.value('(ResponseSet/text())[1]', 'varchar(50)') as RESPONSESET,
  R.X.value('(Name/text())[1]', 'varchar(50)') as NAME
FROM  XmlSourceTable
  CROSS APPLY XmlData.nodes('//Response') AS R(X)