讀取XML資料時為不明確的父級提供唯一識別符號

我有一個 XML 檔案，我已經針對這個問題進行了簡化，格式如下：

<?xml version="1.0"?>
<xml>
    <aggregateddata>
        <aggregateddata>
            <item value="abcdefg1" name="id"/>
            <item value="1" name="dataSet"/>
            <item value="Aggregates" name="dataSetLabel"/>
            <item value="Physical Flow" name="indicator"/>
            <item value="day" name="periodType"/>
            <item value="2021-10-16T06:00:00 02:00" name="periodFrom"/>
            <item value="2021-10-17T06:00:00 02:00" name="periodTo"/>
            <item value="BE" name="countryKey"/>
            <item value="L-Zone" name="bzShort"/>
        </aggregateddata>
        <aggregateddata>
            <item value="abcdefg2" name="id"/>
            <item value="1" name="dataSet"/>
            <item value="Aggregates" name="dataSetLabel"/>
            <item value="Physical Flow" name="indicator"/>
            <item value="day" name="periodType"/>
            <item value="2021-10-16T06:00:00 02:00" name="periodFrom"/>
            <item value="2021-10-17T06:00:00 02:00" name="periodTo"/>
            <item value="UK" name="countryKey"/>
            <item value="L-Zone" name="bzShort"/>
        </aggregateddata>
    </aggregateddata>
</xml>

我希望能夠閱讀此內容，理想情況下將每組值都放在自己的行上。把它變成對 SQL 服務器更友好的東西 像這樣：

為此，我雖然可以使用以下方法讀取 XML：

select
    XMLDataNodes.x.value('@name', 'varchar(50)') as FieldName,
    XMLDataNodes.x.value('@value', 'varchar(500)') as FieldValue
from 
    @XmlFile.nodes ('/xml/aggregateddata/aggregateddata/item') as XMLDataNodes(x)

然后使用某種動態樞軸將結果轉換為我需要的結果。

但問題是，沒有什么我可以“分組”的——在父注釋上基本上沒有任何價值可以放在它們旁邊。我試過這樣的東西https://www.sqlservercentral.com/forums/topic/how-to-uniquely-number-parent-and-child-nodes-while-reading-an-xml-document，添加一個識別符號到每個組，但在實際的完整資料集上速度非常慢：

select
    XMLDataNodes.x.value('@name', 'varchar(50)') as FieldName,
    XMLDataNodes.x.value('@value', 'varchar(500)') as FieldValue,
    XMLNodes.x.value('1 count(for $a in . return $a/../*[. << $a])','int') as parentID
from 
    @XmlFile.nodes ('/xml/aggregateddata/aggregateddata') as XMLNodes(x)
    cross apply XMLNodes.x.nodes('item') as XMLDataNodes(x)

有沒有辦法讓 ID 值更快，或者直接拉出資料，而不需要這一步（或之后的樞軸）？

uj5u.com熱心網友回復：

請嘗試以下解決方案。

SQL Server 的 XQuery 非常強大。

主要思想是將 XPath 與謂詞一起使用：

item[@name="..."]/@value

SQL

DECLARE @xml XML =
N'<xml>
    <aggregateddata>
        <aggregateddata>
            <item value="abcdefg1" name="id"/>
            <item value="1" name="dataSet"/>
            <item value="Aggregates" name="dataSetLabel"/>
            <item value="Physical Flow" name="indicator"/>
            <item value="day" name="periodType"/>
            <item value="2021-10-16T06:00:00 02:00" name="periodFrom"/>
            <item value="2021-10-17T06:00:00 02:00" name="periodTo"/>
            <item value="BE" name="countryKey"/>
            <item value="L-Zone" name="bzShort"/>
        </aggregateddata>
        <aggregateddata>
            <item value="abcdefg2" name="id"/>
            <item value="1" name="dataSet"/>
            <item value="Aggregates" name="dataSetLabel"/>
            <item value="Physical Flow" name="indicator"/>
            <item value="day" name="periodType"/>
            <item value="2021-10-16T06:00:00 02:00" name="periodFrom"/>
            <item value="2021-10-17T06:00:00 02:00" name="periodTo"/>
            <item value="UK" name="countryKey"/>
            <item value="L-Zone" name="bzShort"/>
        </aggregateddata>
    </aggregateddata>
</xml>';

SELECT c.value('(item/@value)[1]', 'varchar(50)') as id
    , c.value('(item[@name="dataSet"]/@value)[1]', 'varchar(500)') as dataSet
    , c.value('(item[@name="dataSetLabel"]/@value)[1]', 'varchar(500)') as dataSetLabel
    , c.value('(item[@name="indicator"]/@value)[1]', 'varchar(500)') as indicator
    , c.value('(item[@name="periodType"]/@value)[1]', 'varchar(500)') as periodType
    , c.value('(item[@name="periodFrom"]/@value)[1]', 'datetimeoffset(0)') as periodFrom
    , c.value('(item[@name="periodTo"]/@value)[1]', 'datetimeoffset(0)') as periodTo
    , c.value('(item[@name="countryKey"]/@value)[1]', 'CHAR(2)') as countryKey
    , c.value('(item[@name="bzShort"]/@value)[1]', 'VARCHAR(20)') as bzShort
FROM @xml.nodes('/xml/aggregateddata/aggregateddata') as t(c);

輸出

 ---------- --------- -------------- --------------- ------------ ---------------------------- ---------------------------- ------------ --------- 
|    id    | dataSet | dataSetLabel |   indicator   | periodType |         periodFrom         |          periodTo          | countryKey | bzShort |
 ---------- --------- -------------- --------------- ------------ ---------------------------- ---------------------------- ------------ --------- 
| abcdefg1 |       1 | Aggregates   | Physical Flow | day        | 2021-10-16 06:00:00  02:00 | 2021-10-17 06:00:00  02:00 | BE         | L-Zone  |
| abcdefg2 |       1 | Aggregates   | Physical Flow | day        | 2021-10-16 06:00:00  02:00 | 2021-10-17 06:00:00  02:00 | UK         | L-Zone  |
 ---------- --------- -------------- --------------- ------------ ---------------------------- ---------------------------- ------------ --------- 

uj5u.com熱心網友回復：

這樣的事情可能會有所幫助（并且至少應該比動態 XQuery 更快）：

select 
    XmlDataNodes.x.value('(item[@name="id"]/@value)[1]', 'varchar(500)') as Id,
    Items.*
from 
    @XmlFile.nodes ('/xml/aggregateddata/aggregateddata') as XMLDataNodes(x)
    cross apply (
       select
         ItemNodes.x.value('@name', 'varchar(50)') as FieldName,
         ItemNodes.x.value('@value', 'varchar(500)') as FieldValue
       from XMLDataNodes.x.nodes('item') ItemNodes(x)
    ) Items

結果：

標籤：sql-server xml 查询语句 查询

上一篇：如何在一個字串的最后三分之一處添加一個字符？

下一篇：復制行并替換字串