CODEWITHSUNDEEP
sqlxmlsql-server-2008-r2
user3170410
user3170410

Reputation: 61

SQL Query to parse XML data to multiple columns

I am new to working with XML. I have reviewed several posts and am having a hard time wrapping my head around this task.

I have a SQL Server 2008R2 table named tbl.AccountLogs with columns - Source, Date, ID and Details (xml data)

I am trying to parse through XML field "Details" into several columns. The field can have numerous tags and nested tags.

Here is an example:

<errors>
<error date = '1394746874'>
    <detail id = '3342867234'>
        <arg type = '0'><![CDATA[512,107,741,184]]></arg>
        <arg type = '0'><![CDATA[274,877,906,944]]></arg>
        <arg type = '0'><![CDATA[J:\]]></arg>
        <arg type = '0'><![CDATA[TestLine.img]]></arg>
    </detail>
</error>

<error date = '1394747022'>
    <detail id = '3342867234'>
        <arg type = '0'><![CDATA[28,594,585,600]]></arg>
        <arg type = '0'><![CDATA[21,474,836,480]]></arg>
        <arg type = '0'><![CDATA[C:\Safe\]]></arg>
        <arg type = '0'><![CDATA[{BigMonster}]]></arg>
    </detail>
</error>

</errors>

As mentioned before, the number of "errors" can change per record, but the "arg type" and "detail id" are consistent.

Example output would be: enter image description here

Any assistance would be greatly appreciated!

Upvotes: 0

Views: 1780

Answers (1)

Napstar
Napstar

Reputation: 363

Try this, hope it helps you move in the right direction:

--if temp table exists drop it
    IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
go
CREATE TABLE #tmp ( your_xml NVARCHAR(MAX) )

GO
--insert data
INSERT INTO #tmp SELECT '<errors>
<error date = ''1394746874''>
    <detail id = ''3342867234''>
        <arg type = ''0''><![CDATA[512,107,741,184]]></arg>
        <arg type = ''0''><![CDATA[274,877,906,944]]></arg>
        <arg type = ''0''><![CDATA[J:\]]></arg>
        <arg type = ''0''><![CDATA[TestLine.img]]></arg>
    </detail>
</error>

<error date = ''1394747022''>
    <detail id = ''3342867234''>
        <arg type = ''0''><![CDATA[28,594,585,600]]></arg>
        <arg type = ''0''><![CDATA[21,474,836,480]]></arg>
        <arg type = ''0''><![CDATA[C:\Safe\]]></arg>
        <arg type = ''0''><![CDATA[{BigMonster}]]></arg>
    </detail>
</error>

</errors>'--END of xml


SELECT 

   'Record'+Convert(nvarchar(50), (ROW_NUMBER() OVER (ORDER BY x.y.value('(./detail[1]/arg)[1]', 'nvarchar(100)')))) AS [Source],
    x.y.value('@date', 'nvarchar(100)') AS [Date],
    x.y.value('./detail[1]/@id', 'nvarchar(100)') AS ID,
    x.y.value('(./detail[1]/arg)[1]', 'nvarchar(100)') AS Detail1,
    x.y.value('(./detail[1]/arg)[2]', 'nvarchar(100)') AS Detail2,
    x.y.value('(./detail[1]/arg)[3]', 'nvarchar(100)') AS Detail3,
    x.y.value('(./detail[1]/arg)[4]', 'nvarchar(100)') AS Detail4

FROM
    (
    SELECT CAST( your_xml AS XML ) AS your_xml
    FROM #tmp
    ) t
    CROSS APPLY t.your_xml.nodes('//errors/error') x(y)

Basically you use CROSS APPLY and using the Properties.value to retrieve the values of the XML.

Upvotes: 1

Related Questions