0

I want to import below XML file into SQL table. (SQL Server 2008R2)

<table>
  <id>{72cbb5ab-dbb3-4de7-9010-5dd1192a1851}</id>
  <rows>
    <row>
      <columns>
        <column name="itemcode" value="0984-22-301" type="System.String" />
        <column name="date" value="08-November-2017" type="System.DateTime" />
        <column name="amount" value="10" type="System.Decimal" />
        <column name="DefaultKey" value="1" type="System.Int32" />
      </columns>
    </row>
    <row>
      <columns>
        <column name="itemcode" value="0984-33-101" type="System.String" />
        <column name="date" value="08-November-2017" type="System.DateTime" />
        <column name="amount" value="11" type="System.Decimal" />
        <column name="DefaultKey" value="2" type="System.Int32" />
      </columns>
    </row>
  </rows>
  <key>DefaultKey</key>
  <total>0</total>
  <data />
  <parameters />
</table>

It should look like a sql table with columns id, itemcode, date and amount.

How should my query look like?

jeuris
  • 1
  • 1

1 Answers1

0

Solved the question.

declare @xmltable table (data xml)
insert into @xmltable (data)
select DATA from [MyData]..myxml 


SELECT
    LineId = c.value('id[1]', 'nvarchar(max)'),

    ColumnItemCode = l.value('(columns/column[@name="itemcode"]/@value)[1]', 'varchar(20)'),
    ColumnDate = l.value('(columns/column[@name="date"]/@value)[1]', 'varchar(20)'),
    ColumnAmount = l.value('(columns/column[@name="amount"]/@value)[1]', 'varchar(20)')
FROM
    @xmltable x 
    CROSS APPLY data.nodes('table') t(c)
    CROSS APPLY data.nodes('table/rows/row') b(l)

This resulted in:

LineId                                  ColumnItemCode  ColumnDate   ColumnAmount
{72cbb5ab-dbb3-4de7-9010-5dd1192a1851}  0984-22-301     08-November-2017    10
{72cbb5ab-dbb3-4de7-9010-5dd1192a1851}  0984-33-101     08-November-2017    11

Thanks for your help.

jeuris
  • 1
  • 1