問題描述
我正在嘗試使用 SQL 查詢 XML.假設我有以下 XML.
<數據集數據><text>ABC</text></dataSetData><一般數據><id>123</id><text>文本數據</text></generalData><一般數據><id>456</id><text>文本數據2</text></generalData><特殊數據><id>123</id><text>特殊數據文本</text></specialData><特殊數據><id>456</id><text>特殊數據文本2</text></specialData></xml> 我想編寫一個返回 2 行的 SELECT 查詢，如下所示:
DataSetData |通用數據ID |通用數據文本 |特殊數據測試ABC |123 |文本數據 |特殊數據文本ABC |第456話文本數據 2 |特殊數據文本 2我目前的做法如下:
SELECTdataset.nodes.value('(dataSetData/text)[1]', 'nvarchar(500)'),general.nodes.value('(generalData/text)[1]', 'nvarchar(500)'),special.nodes.value('(specialData/text)[1]', 'nvarchar(500)'),FROM @MyXML.nodes('xml') AS dataset(nodes)外部應用@MyXML.nodes('xml/generalData') AS general(nodes)外部應用@MyXML.nodes('xml/specialData') AS special(nodes)在哪里general.nodes.value('(generalData/text/id)[1]', 'nvarchar(500)') = special.nodes.value('(specialData/text/id)[1]', 'nvarchar(500))')我不喜歡這里的是我必須使用 OUTER APPLY 兩次，而且我必須使用 WHERE 子句來 JOIN正確的元素.
因此我的問題是: 是否有可能以我不必以這種方式使用 WHERE 子句的方式構造查詢，因為我是可以肯定的是，如果文件變大，這會對性能產生非常負面的影響.
難道不能用一些XPATH語句JOIN正確的節點(即對應的generalData和specialData節點)?
您的 XPath 表達式完全關閉.
請嘗試以下操作.這是非常有效的.您可以使用大型 XML 測試其性能.
<塊引用>SQL
-- DDL和樣本數據填充，開始聲明@xml XML =N'<xml><數據集數據><text>ABC</text></dataSetData><一般數據><id>123</id><text>文本數據</text></generalData><一般數據><id>456</id><text>文本數據2</text></generalData><特殊數據><id>123</id><text>特殊數據文本</text></specialData><特殊數據><id>456</id><text>特殊數據文本2</text></specialData></xml>';-- DDL和樣本數據填充，結束SELECT c.value('(dataSetData/text/text())[1]', 'VARCHAR(20)') AS DataSetData, g.value('(id/text())[1]', 'INT') AS GeneralDataID, g.value('(text/text())[1]', 'VARCHAR(30)') AS GeneralDataText, sp.value('(id/text())[1]', 'INT') AS SpecialDataID, sp.value('(text/text())[1]', 'VARCHAR(30)') AS SpecialDataTestFROM @xml.nodes('/xml') AS t(c)外部應用 c.nodes('generalData') AS general(g)外部應用 c.nodes('specialData') AS special(sp)WHERE g.value('(id/text())[1]', 'INT') = sp.value('(id/text())[1]', 'INT');<塊引用>
輸出
+-------------+---------------+-----------------+----------------------------+------------+|數據集數據 |通用數據ID |通用數據文本 |特殊數據ID |特殊數據測試 |+-------------+---------------+-----------------+---------------+--------------+|ABC |123 |文本數據 |123 |特殊數據文本 ||ABC |第456話文本數據 2 |第456話特殊數據文本 2 |+-------------+---------------+-----------------+---------------+--------------+I am trying to query XML with SQL. Suppose I have the following XML.
<xml>
<dataSetData>
<text>ABC</text>
</dataSetData>
<generalData>
<id>123</id>
<text>text data</text>
</generalData>
<generalData>
<id>456</id>
<text>text data 2</text>
</generalData>
<specialData>
<id>123</id>
<text>special data text</text>
</specialData>
<specialData>
<id>456</id>
<text>special data text 2</text>
</specialData>
</xml>
I want to write a SELECT query that returns 2 rows as follows:
DataSetData | GeneralDataID | GeneralDataText | SpecialDataTest
ABC | 123 | text data | special data text
ABC | 456 | text data 2 | special data text 2
My current approach is as follows:
SELECT
dataset.nodes.value('(dataSetData/text)[1]', 'nvarchar(500)'),
general.nodes.value('(generalData/text)[1]', 'nvarchar(500)'),
special.nodes.value('(specialData/text)[1]', 'nvarchar(500)'),
FROM @MyXML.nodes('xml') AS dataset(nodes)
OUTER APPLY @MyXML.nodes('xml/generalData') AS general(nodes)
OUTER APPLY @MyXML.nodes('xml/specialData') AS special(nodes)
WHERE
general.nodes.value('(generalData/text/id)[1]', 'nvarchar(500)') = special.nodes.value('(specialData/text/id)[1]', 'nvarchar(500)')
What I do not like here is that I have to use OUTER APPLY twice and that I have to use the WHERE clause to JOIN the correct elements.
My question therefore is: Is it possible to construct the query in a way where I do not have to use the WHERE clause in such a way, because I am pretty sure that this affects performance very negatively if files become larger.
Shouldn't it be possible to JOIN the correct nodes (that is, the corresponding generalData and specialData nodes) with some XPATH statement?
Your XPath expressions are completely off.
Please try the following. It is pretty efficient. You can test its performance with a large XML.
SQL
-- DDL and sample data population, start
DECLARE @xml XML =
N'<xml>
<dataSetData>
<text>ABC</text>
</dataSetData>
<generalData>
<id>123</id>
<text>text data</text>
</generalData>
<generalData>
<id>456</id>
<text>text data 2</text>
</generalData>
<specialData>
<id>123</id>
<text>special data text</text>
</specialData>
<specialData>
<id>456</id>
<text>special data text 2</text>
</specialData>
</xml>';
-- DDL and sample data population, end
SELECT c.value('(dataSetData/text/text())[1]', 'VARCHAR(20)') AS DataSetData
, g.value('(id/text())[1]', 'INT') AS GeneralDataID
, g.value('(text/text())[1]', 'VARCHAR(30)') AS GeneralDataText
, sp.value('(id/text())[1]', 'INT') AS SpecialDataID
, sp.value('(text/text())[1]', 'VARCHAR(30)') AS SpecialDataTest
FROM @xml.nodes('/xml') AS t(c)
OUTER APPLY c.nodes('generalData') AS general(g)
OUTER APPLY c.nodes('specialData') AS special(sp)
WHERE g.value('(id/text())[1]', 'INT') = sp.value('(id/text())[1]', 'INT');
Output
+-------------+---------------+-----------------+---------------+---------------------+
| DataSetData | GeneralDataID | GeneralDataText | SpecialDataID | SpecialDataTest |
+-------------+---------------+-----------------+---------------+---------------------+
| ABC | 123 | text data | 123 | special data text |
| ABC | 456 | text data 2 | 456 | special data text 2 |
+-------------+---------------+-----------------+---------------+---------------------+
這篇關于SQL Server XML 處理:根據 ID 加入不同的節點的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！