时间戳

首页 > 解决方案 > SQL - 如何在 XML 标记中插入唯一 ID

sql - SQL - 如何在 XML 标记中插入唯一 ID

问题描述

我从外部系统获得固定的 XML，我需要在 T-SQL 中在<document>标签中附加一些唯一 ID 并将所有数据插入到 2 个表中。

这是我得到的 XML：

<root>
    <document number="1234">
        <data1>
            <item1 d="100"/> 
            <item1 d="200"/> 
        </data1>
        <data2>
            <item2 d="111"/> 
            <item2 d="222"/> 
        </data2>
    </document> 
    <document number="1234">
        <data1>
            <item1 d="300"/> 
            <item1 d="400"/> 
        </data1>
    </document> 
    <document number="1234">
        <data1>
            <item1 d="500"/> 
        </data1>
        <data2>
            <item2 d="555"/> 
        </data2>
    </document> 
</root>

我需要将上述 XML 插入到 2 个表中tData1，并且在该级别tData2具有唯一的 docid 。<document>

表tData1应如下所示：

docid    number     d
---------------------
    1      1234   100
    1      1234   200
    2      1234   300
    2      1234   400
    3      1234   500

表tData2应如下所示：

docid    number    d
---------------------
    1      1234   111
    1      1234   222
    3      1234   555

如何docid在将上述 XML 转换为这些表时插入字段？

标签： sqlxmltsqlxml-parsing

解决方案

您可以nodes在 a中使用CROSS APPLY，如下所示：

DECLARE @x XML='<root>
    <document number="1234">
        <data1>
            <item1 d="100"/> 
            <item1 d="200"/> 
        </data1>
        <data2>
            <item2 d="111"/> 
            <item2 d="222"/> 
        </data2>
    </document> 
    <document number="1234">
        <data1>
            <item1 d="300"/> 
            <item1 d="400"/> 
        </data1>
    </document> 
    <document number="1234">
        <data1>
            <item1 d="500"/> 
        </data1>
        <data2>
            <item2 d="555"/> 
        </data2>
    </document> 
</root>'


SELECT Q1.docid, Q1.number, Q2.d
FROM (
    SELECT  ROW_NUMBER() OVER (ORDER BY T1.N1) AS docid,
            N1.value('@number','varchar(10)') AS number,
            N1.query('.') AS X
    FROM @x.nodes('root/document') T1(N1)
) Q1
CROSS APPLY (
    SELECT N2.value('@d','varchar(10)') AS d
    FROM Q1.x.nodes('document/data1/item1') T2(N2)
) Q2

SELECT Q1.docid, Q1.number, Q2.d
FROM (
    SELECT  ROW_NUMBER() OVER (ORDER BY T1.N1) AS docid,
            N1.value('@number','varchar(10)') AS number,
            N1.query('.') AS X
    FROM @x.nodes('root/document') T1(N1)
) Q1
CROSS APPLY (
    SELECT N2.value('@d','varchar(10)') AS d
    FROM Q1.x.nodes('document/data2/item2') T2(N2)
) Q2

为了生成docid，我在节点列上使用了 ROW_NUMBER，如http://dataeducation.com/uniquely-identifying-xml-nodes-with-dense_rank/中所述。

推荐阅读