sql - 将 xml 递归地分解到数据库中

Question

我有以下 XML 数据和元素表。

DECLARE @input XML = '<root>
     <C1>
       <C2>
         <C3>           <C4>data1</C4>       </C3>         
       </C2>
       <C2>
         <C3>data2</C3>
       </C2>
     </C1>
     <D1>
        <D2>data3</D2>
        <D2>data4</D2>
     </D1>
    </root>'

元素表:(这只是一个示例，因此可以更改以匹配适当的解决方案。)

CREATE TABLE Element (  elementId INT IDENTITY PRIMARY KEY, 
elementName VARCHAR (200) NOT NULL, 
parentId INT,   
data VARCHAR(300) );

根据@input，根元素是C1和D1的父元素，那么C1是C2的父元素，...

SQL Server 2012/2014 使用 CTE(或任何其他类型的 SQL 对象)编写存储过程以递归地将所有元素名称放入 Element 表中的解决方案是什么？

在这种情况下，数据列填充数据，C4 和第二个 C3，D2 元素有数据，其余元素为空。

我还看到了分层数据类型，我想知道这是否有助于解决这个问题？

Answer 1

与 OpenXML您可以使用 metaproperties 获得包含 ID 和 ParentID 列的 XML 表表示形式.

在 merge 中使用 XML 查询将允许您 create a mapping elementId 身份列和 XML 中的 DOM 节点 ID 之间的表。

最后一步是使用映射表更新Element中的parentId。

SQL Fiddle

MS SQL Server 2008 架构设置:

CREATE TABLE Element (  elementId INT IDENTITY PRIMARY KEY, 
elementName VARCHAR (200) NOT NULL, 
parentId INT,   
data VARCHAR(300) );

查询 1:

declare @input xml = '
<root>
  <C1>
    <C2>
      <C3>
        <C4>data1</C4>
      </C3>
    </C2>
    <C2>
      <C3>data2</C3>
    </C2>
  </C1>
  <D1>
    <D2>data3</D2>
    <D2>data4</D2>
  </D1>
</root>';

-- OpenXML handle
declare @D int;

-- Table that capture output of merge with mapping between 
-- DOM node id and the identity column elementID in Element 
declare @T table
(
  ID int,
  ParentID int,
  ElementID int
);

-- Parse XML and get a handle
exec sp_xml_preparedocument @D output, @input;

-- Add rows to Element and fill the mapping table @T
merge into dbo.Element as E
using ( 
      select *
      from openxml(@D, '//*') with 
        (
          ID int '@mp:id',
          ParentID int '@mp:parentid',
          Data varchar(300) 'text()',
          ElementName varchar(200) '@mp:localname'
        )
      ) as S
on 0 = 1
when not matched by target then
  insert (elementName, data) values (S.ElementName, S.data)
output S.ID, S.ParentID, inserted.elementID into @T;

-- Update parentId in Elemet
update E
set parentId =  T2.ElementID
from dbo.Element as E
  inner join @T as T1
    on E.elementId = T1.ElementID
  inner join @T as T2
    on T1.ParentID = T2.ID


-- Relase the XML document
exec sp_xml_removedocument @D;

select *
from Element;

Results :

| ELEMENTID | ELEMENTNAME | PARENTID |   DATA |
|-----------|-------------|----------|--------|
|         1 |        root |   (null) | (null) |
|         2 |          C1 |        1 | (null) |
|         3 |          C2 |        2 | (null) |
|         4 |          C3 |        3 | (null) |
|         5 |          C4 |        4 |  data1 |
|         6 |          C2 |        2 | (null) |
|         7 |          C3 |        6 |  data2 |
|         8 |          D1 |        1 | (null) |
|         9 |          D2 |        8 |  data3 |
|        10 |          D2 |        8 |  data4 |