使用OpenXML，您可以使用metaproperties使用ID和ParentID列获取XML的表表示形式。
在合并中使用XML查询将使您可以在XML的标识列和DOM节点ID之间创建映射表elementId。
最后一步是使用映射表来更新parentId在Element。
：
CREATE TABLE Element ( elementId INT IDENTITY PRIMARY KEY,
elementName VARCHAR (200) NOT NULL,
parentId INT,
data VARCHAR(300) );
：
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 |
602392714
清零编程群