我有以下XML数据和Element表:
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对象)编码存储过程来递归地将所有元素名称放入元素表中?
数据列填充数据,在本例中,C4和第二个C3和D2元素具有数据,元素的其余部分为空。
我也看到了分层数据类型,我想知道这是否有助于解决这个问题?
使用OpenXML,您可以使用元属性获得包含ID
和ParentID
列的XML表表示。
在合并中使用XML查询将允许您在elementId
标识列和来自XML的DOM节点id之间创建映射表。
最后一步是使用映射表更新Element
中的parentId
。
MS SQL Server 2008 Schema Setup:
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 |