sql - 将两级 XML 列表中的值提取到 SQL 列中

Question

我有一个 SQL Server 2008 数据库，我从不同的表中提取多个值以放入单个表中。在这些值中有一些数据是从 XML 中提取的，直到最近才存储在单个级别上，如下所示:

<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>

我将使用以下方法提取必要的信息:

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)

哪个会返回:

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

但是，现在 XML 列表已更改并在另一个列表中生成，如下所示:

<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>

我需要生成如下所示的信息:

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

我将如何着手修改我的查询以适应结构的变化？感谢您的帮助。

Answer 1

您可以使用嵌套的 nodes() 方法来实现:

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)

或者使用父轴(..):

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

---

更新:

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