How to insert XML to SQL

Question

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!

Andrey Korneyev · Accepted Answer · 2015-04-27 12:01:59Z

3

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

answered Apr 27, 2015 at 12:01

Andrey Korneyev

26.9k16 gold badges73 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

George T · Accepted Answer · 2015-04-27 12:06:25Z

2

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)

answered Apr 27, 2015 at 12:06

George T

8578 silver badges16 bronze badges

1 Comment

MeTa Over a year ago

Thank you very much! I will learn more about CROSS APPLY is new for me :)

Collectives™ on Stack Overflow

How to insert XML to SQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related