以递归方式将 xml 粉碎到数据库中

2021-10-01 00:00:00 xml sql xquery sql-server xquery-sql

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

I have the following XML data and the Element table.

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

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

Element table:( this is just an example so can be changed to match an appropriate solution.)

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

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

According to @input the root element is parent of C1 and D1, then C1 is C2 parent, ...

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

What is the solution for SQL server 2012/2014 to code a stored procedure with CTE (or any other type of SQL object) to recursively put all element names into the Element table?

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

data column fills with data in this case, the C4 and the second C3, and D2 elements have data the rest of element are null.

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

I also saw Hierarchical Data type and I wonder if that could be helpful to solve this problem?

推荐答案

With OpenXML 您可以使用 元属性.

With OpenXML you can get a table representation of your XML with ID and ParentID columns using the metaproperties.

在合并中使用 XML 查询将允许您创建映射表elementId 标识列和来自 XML 的 DOM 节点 ID 之间.

Using the XML query in a merge will allow you to create a mapping table between the elementId identity column and the DOM node id from the XML.

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

The last step is to use the mapping table to update parentId in Element.

SQL 小提琴

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;

结果:

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

相关文章