CODEWITHSUNDEEP
sqlsql-serverxmlsql-server-2008
MeTa
MeTa

Reputation: 89

How to insert XML to SQL

I try to add a XML file to SQL 2008.

My XML:

<ItemList>
    <Section Index="0" Name="cat0">
        <Item Index="0" Slot="0" />
        <Item Index="1" Slot="0" />
    </Section>
    <Section Index="1" Name="cat1">
        <Item Index="33" Slot="0" />
        <Item Index="54" Slot="0" />
    </Section>
        <Section Index="2" Name="cat2">
        <Item Index="55" Slot="0" />
        <Item Index="78" Slot="0" />
    </Section>
</ItemList>

SQL Column :

Name = Section Name,
Cat = Section Index,
Index = Item Index,
Slot = Item Slot.

My Example : 

DECLARE @input XML = 'MY XML file'

SELECT
      Name = XCol.value('@Index','varchar(25)'),
      Cat = XCol.value('@Name','varchar(25)'),
      [Index] = 'Unknown', /* Index from <Item>*/
      Slot = 'Unknown' /* Slot from <Item> */
FROM @input.nodes('/ItemList/Section') AS test(XCol)

I don't know how to add values from "Item".

Thank you very much!

Upvotes: 2

Views: 125

Answers (2)

George T
George T

Reputation: 857

Different than the previous answer - CROSS APPLY with the children Item nodes:

SELECT
      Name = XCol.value('@Index','varchar(25)'),
      Cat = XCol.value('@Name','varchar(25)'),
      [Index] = XCol2.value('@Index','varchar(25)'),
      Slot = XCol2.value('@Slot','varchar(25)')
FROM @input.nodes('/ItemList/Section') AS test(XCol)
    CROSS APPLY XCol.nodes('Item') AS test2(XCol2)

Upvotes: 2

Andrey Korneyev
Andrey Korneyev

Reputation: 26886

You can do it like this:

select
  Name = XCol.value('../@Index','varchar(25)'),
  Cat = XCol.value('../@Name','varchar(25)'),
  [Index] = XCol.value('@Index','varchar(25)'),
  Slot = XCol.value('@Slot','varchar(25)')
from
  @input.nodes('/ItemList/Section/Item') AS test(XCol)

Key idea: take data one level deeper, not /ItemList/Section, but /ItemList/Section/Item. So in this case you are able to access attributes of Item and also you can access attributes of parent element (Section in your case) by specifying ../@Attribute_Name

Upvotes: 3

Related Questions