問題描述

我有以下 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 |

這篇關于以遞歸方式將 xml 粉碎到數據庫中的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！