CODEWITHSUNDEEP
sqlsql-serverxmlattributes
Rizwan Shaikh
Rizwan Shaikh

Reputation: 95

Save XML with attribute to Table in SQL Server

Hi I have XML data with attribute as input for SQL, i need this to be inserted in my table. XML Data is

<?xml version="1.0" encoding="ISO-8859-1"?>
<MESSAGEACK>

<GUID GUID="kfafb30" SUBMITDATE="2015-10-15 11:30:29" ID="1">
<ERROR SEQ="1" CODE="28681" />
</GUID>

<GUID GUID="kfafb3" SUBMITDATE="2015-10-15 11:30:29" ID="1">
<ERROR SEQ="2" CODE="286381" />
</GUID>

</MESSAGEACK>

I want this to be inserted in below Format

GUID         SUBMIT DATE          ID          ERROR SEQ     CODE
kfafb3   2015-10-15 11:30:29      1          1             28681
kfafb3   2015-10-15 11:30:29      1          1             2868

please help.

Upvotes: 2

Views: 146

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

just paste this into an empty query window and execute. Adapt to your needs:

DECLARE @xml XML=
'<?xml version="1.0" encoding="ISO-8859-1"?>
<MESSAGEACK>

<GUID GUID="kfafb30" SUBMITDATE="2015-10-15 11:30:29" ID="1">
<ERROR SEQ="1" CODE="28681" />
</GUID>

<GUID GUID="kfafb3" SUBMITDATE="2015-10-15 11:30:29" ID="1">
<ERROR SEQ="2" CODE="286381" />
</GUID>

</MESSAGEACK>';

SELECT Msg.Node.value('@GUID','varchar(max)') AS [GUID] --The value is no GUID, if the original values are, you could use uniqueidentifier instead of varchar(max)
      ,Msg.Node.value('@SUBMITDATE','datetime') AS SUBMITDATE
      ,Msg.Node.value('@ID','int') AS ID
      ,Msg.Node.value('(ERROR/@SEQ)[1]','int') AS [ERROR SEQ]
      ,Msg.Node.value('(ERROR/@CODE)[1]','int') AS CODE
FROM @xml.nodes('/MESSAGEACK/GUID') AS Msg(Node)

Upvotes: 2

har07
har07

Reputation: 89285

Look into XPath and xml Data Type Methods in MSDN. This is one possible way :

declare @xml As XML = '...you XML string here...'
INSERT INTO YourTable
SELECT 
    guid.value('@GUID', 'varchar(100)') as 'GUID'
    ,guid.value('@SUBMITDATE', 'datetime') as 'SUBMIT DATE'
    ,guid.value('@ID', 'int') as 'ID'
    ,guid.value('ERROR[1]/@SEQ', 'int') as 'SEQ'
    ,guid.value('ERROR[1]/@CODE', 'int') as 'CODE'
FROM @xml.nodes('/MESSAGEACK/GUID') as x(guid)

Result :

enter image description here

Upvotes: 4

Related Questions