Extracting Values From Two-Level XML List Into SQL Column

Question

I have an SQL server 2008 database from which I am extracting multiple values from various tables to put into a single table. Among these values is some data pulled from XML that until recently was stored on a single level like so:

<XMLData>
  <Item>
    <Name>Name1</Name>
    <Value>Value1</Value>
  </Item>
  <Item>
    <Name>Name2</Name>
    <Value>Value2</Value>
  </Item>
  <Item>
    <Name>Name3</Name>
    <Value>Value3</Value>
  </Item>
  <Item>
    <Name>Name4</Name>
    <Value>Value4</Value>
  </Item>
</XMLData>

I would extract the necessary information with the following method:

SELECT
   Name = IXML.value('(./Name)[1]', 'varchar(20)'),
   Value = IXML.value('(./Value)[1]', 'varchar(20)')
INTO dbo.newTable
FROM dbo.oldTable
CROSS APPLY oldTable.InfoXML.nodes('/XMLData/item') Book(IXML)

Which would return:

Name    Value
--------------
Name1   Value1
Name2   Value2
Name3   Value3
Name4   Value4

However, now the XML list has been altered and is generated within another list like so:

<XMLData>
<LongDirectory>
  <Category>
    <Item>
      <CategoryName>Cat1</CategoryName>
      <SubCategory>
        <Item>
          <Name>Name1</Name>
          <Value>Value1</Value>
        </Item>
        <Item>
          <Name>Name2</Name>
          <Value>Value2</Value>
        </Item>
        <Item>
          <Name>Name3</Name>
          <Value>Value3</Value>
        </Item>
      </SubCategory>
    </Item>
    <Item>
      <CategoryName>Cat2</CategoryName>
      <SubCategory>
        <Item>
          <Name>Name4</Name>
          <Value>Value4</Value>
        </Item>
        <Item>
          <Name>Name5</Name>
          <Value>Value5</Value>
        </Item>
      </SubCategory>
    </Item>
    <Item>
      <CategoryName>Cat3</CategoryName>
      <SubCategory>
        <Item>
          <Name>Name6</Name>
          <Value>Value6</Value>
        </Item>
        <Item>
          <Name>Name7</Name>
          <Value>Value7</Value>
        </Item>
      </SubCategory>
    </Item>
  </Category>
</LongDirectory>
</XMLData>

And I need to generate the information to look like this:

Name    Value    Category
-------------------------
Name1   Value1   Cat1
Name2   Value2   Cat1
Name3   Value3   Cat1
Name4   Value4   Cat2
Name5   Value5   Cat2
Name6   Value6   Cat3
Name7   Value7   Cat3

How would I go about modifying my query to accommodate the change in structure? Any help is appreciated.

Roman Pekar · Accepted Answer · 2013-10-30T18:46:45.500

0

You can do it with nested nodes() methods:

select
    I.C.value('(Name)[1]', 'varchar(20)') as Name,
    I.C.value('(Value)[1]', 'varchar(20)') as Value,
    C.C.value('(CatName)[1]', 'varchar(20)') as Category
-- into dbo.newTable
from dbo.oldTable as T
    cross apply T.InfoXML.nodes('XMLData/Category') as C(C)
    cross apply C.C.nodes('Item') as I(C)

Or use parent axis (..):

select
    I.C.value('(Name)[1]', 'varchar(20)') as Name,
    I.C.value('(Value)[1]', 'varchar(20)') as Value,
    I.C.value('(../CatName)[1]', 'varchar(20)') as Category
-- into dbo.newTable
from dbo.oldTable as T
    cross apply T.InfoXML.nodes('XMLData/Category/Item') as I(C)

sql fiddle example

update:

select
    I.C.value('(Name)[1]', 'varchar(20)') as Name,
    I.C.value('(Value)[1]', 'varchar(20)') as Value,
    C.C.value('(CategoryName)[1]', 'varchar(20)') as Category
from dbo.oldTable as T
    outer apply T.InfoXML.nodes('XMLData/LongDirectory/Category/Item') as C(C)
    outer apply C.C.nodes('SubCategory/Item') as I(C)

sql fiddle example

edited Oct 30 '13 at 18:46

answered Oct 29 '13 at 16:31

Roman Pekar

107,110
28
195
197

I used the first method you posted, and it has succeeded in generating all the values, however it seems to be duplicating each entry for each Category value (at least I'm assuming it's relative to the number of Category values based on my test data). So when pulling from a 3 Category XML page all the rows are in triplicate. I recall coming across this issue previously but can't figure out what my solution was. Any ideas? – Justin RLA Oct 30 '13 at 16:10
can you add xml example for which you see such a duplication? – Roman Pekar Oct 30 '13 at 16:41
I've updated the XML in the original question to what it looks like in reality. – Justin RLA Oct 30 '13 at 17:35
In that case how do I identify the table I am pulling the xml from (marked as dbo.oldTable)? The new table pulls various pieces of information from multiple tables (including other columns from the table I'm getting the xml from). – Justin RLA Oct 30 '13 at 18:45
Found the issue with the duplicates. Someone modified another one of the tables I pull from (unrelated to these values) without my knowledge and it was generating duplicates which were affecting the rest of my table, so that was not related to this issue. @Roman Thanks for the help! – Justin RLA Oct 31 '13 at 15:18

Extracting Values From Two-Level XML List Into SQL Column

1 Answers1