本文介紹了如何從 XML 中提取值?的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學習吧！
問題描述
我有下面定義的 XML 變量及其值.請幫忙
I have the XML variable defined below and its value. Please help
DECLARE @xml2 as XML ;
SET @xml2 = '<Student>
<Marks>
<Subject>Science</Subject>
<Score>89</Score>
<Subject>Maths</Subject>
<Score>90</Score>
</Marks>
</Student>'
預期結果應該是:
Subject Score
-------- ------
Science 89
Maths 90
推薦答案
還有一種方法，應該會快一點...
And one more approach, which should be a little faster...
DECLARE @xml2 as XML ;
SET @xml2 = '<Student>
<Marks>
<Subject>Science</Subject>
<Score>89</Score>
<Subject>Maths</Subject>
<Score>90</Score>
</Marks>
</Student>';
WITH tally(Nmbr) AS(SELECT TOP(@xml2.value('count(/Student/Marks/Subject)','int')) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM master..spt_values)
SELECT tally.Nmbr
,@xml2.value('(/Student/Marks/Subject[sql:column("tally.Nmbr")]/text())[1]','nvarchar(max)') AS [Subject]
,@xml2.value('(/Student/Marks/Score[sql:column("tally.Nmbr")]/text())[1]','int') AS Score
FROM tally;
簡單的想法:
- 我們通過使用計算的 TOP 子句和
ROW_NUMBER()對具有較大行數的任何表(我使用 master..spt_values在這里，最好是物理數字表...) - 現在我們可以使用
sql:column()通過位置獲取每個值，以將計數的當前值放入 XQuery. - 這意味著:我們選擇第一個分數第一的科目.比第二個科目的第二個分數等等......
- We create a tally on the fly by using a computed TOP clause together with
ROW_NUMBER()against any table with a larger row count (I use master..spt_values here, best was a physical numbers table...) - Now we can grab each value by its position using
sql:column()to get the tally's current value into the XQuery. - This means: We pick the first Subject with the first Score. Than the second Subject with the second score and so on...
提示:這種格式是非常錯誤的.如果這在你的控制之下，你真的應該改變它.您完全依賴于元素的順序和位置.缺失的元素或任何混淆或介于兩者之間的其他元素都可能將其拆毀.
Hint: This format is very erronous. If this is under your control you really should change it. You are relying completely on the element's order and position. A missing element or any mix-up or other elements in between could tear this down to the ground.
我會使用類似的東西
<Student>
<Marks Subject="Science" Score="80"/>
<Marks Subject="Maths" Score="90"/>
</Student>
或
<Student>
<Marks>
<Subject name="Science">80</Subject>
<Subject name="Maths">90</Subject>
</Marks>
</Student>
更新基準
以下將比較具有奇數/偶數結構的 10/100/1000 對的 XML:
The following will compare a XML with 10 / 100 / 1000 pairs in odd/even structure:
--確保使用數據庫，其中該表返回至少 1000 行(或使用任何其他表)
--Make sure to use a database, where this table returns at least 1000 rows (or use any other table)
SELECT COUNT(*) FROM master..spt_values
--用虛擬數據填充表格
--Filling a table with dummy data
DECLARE @tbl TABLE(ID INT IDENTITY,[Subject] VARCHAR(30),Score VARCHAR(30));
INSERT INTO @tbl
SELECT TOP 1000 LEFT(CAST(NEWID() AS varchar(50)),30),CAST(CAST(NEWID() AS binary(4)) AS INT)
FROM master..spt_values;
SELECT * FROM @tbl;
--使用三個不同對數的 XML
--using three XMLs with different count of pairs
DECLARE @xml10 XML;
DECLARE @xml100 XML;
DECLARE @xml1000 XML;
SET @xml10=(
SELECT TOP 10
(SELECT [Subject] FOR XML PATH(''),TYPE) AS [*]
,(SELECT [Score] FOR XML PATH(''),TYPE) AS [*]
FROM @tbl t
ORDER BY t.ID
FOR XML PATH(''),ROOT('root')
);
SET @xml100=(
SELECT TOP 100
(SELECT [Subject] FOR XML PATH(''),TYPE) AS [*]
,(SELECT [Score] FOR XML PATH(''),TYPE) AS [*]
FROM @tbl t
ORDER BY t.ID
FOR XML PATH(''),ROOT('root')
);
SET @xml1000=(
SELECT TOP 1000
(SELECT [Subject] FOR XML PATH(''),TYPE) AS [*]
,(SELECT [Score] FOR XML PATH(''),TYPE) AS [*]
FROM @tbl t
ORDER BY t.ID
FOR XML PATH(''),ROOT('root')
);
--測試 10
DECLARE @d DATETIME2=SYSUTCDATETIME();
WITH tally(Nmbr) AS(SELECT TOP(@xml10.value('count(/root/Subject)','int')) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM master..spt_values)
SELECT tally.Nmbr
,@xml10.value('(/root/Subject[sql:column("tally.Nmbr")]/text())[1]','nvarchar(max)') AS [Subject]
,@xml10.value('(/root/Score[sql:column("tally.Nmbr")]/text())[1]','nvarchar(max)') AS Score
INTO #t10a
FROM tally;
SELECT 'xml10 a',DATEDIFF(MILLISECOND,@d,SYSUTCDATETIME());
SET @d=SYSUTCDATETIME();
SELECT c.value('(./text())[1]', 'nvarchar(max)') AS [Subject]
, c.value('(/root/*[sql:column("w.r")]/text())[1]', 'nvarchar(max)') AS [Score]
INTO #t10b
FROM @xml10.nodes('/root/*[position() mod 2 = 1]') AS t(c)
CROSS APPLY (SELECT t.c.value('let $n := . return count(/root/*[. << $n[1]]) + 2','INT') AS r
) AS w;
SELECT 'xml10 b',DATEDIFF(MILLISECOND,@d,SYSUTCDATETIME());
--測試 100
SET @d =SYSUTCDATETIME();
WITH tally(Nmbr) AS(SELECT TOP(@xml100.value('count(/root/Subject)','int')) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM master..spt_values)
SELECT tally.Nmbr
,@xml100.value('(/root/Subject[sql:column("tally.Nmbr")]/text())[1]','nvarchar(max)') AS [Subject]
,@xml100.value('(/root/Score[sql:column("tally.Nmbr")]/text())[1]','nvarchar(max)') AS Score
INTO #t100a
FROM tally;
SELECT 'xml100 a',DATEDIFF(MILLISECOND,@d,SYSUTCDATETIME());
SET @d=SYSUTCDATETIME();
SELECT c.value('(./text())[1]', 'nvarchar(max)') AS [Subject]
, c.value('(/root/*[sql:column("w.r")]/text())[1]', 'nvarchar(max)') AS [Score]
INTO #t100b
FROM @xml100.nodes('/root/*[position() mod 2 = 1]') AS t(c)
CROSS APPLY (SELECT t.c.value('let $n := . return count(/root/*[. << $n[1]]) + 2','INT') AS r
) AS w;
SELECT 'xml100 b',DATEDIFF(MILLISECOND,@d,SYSUTCDATETIME());
--測試 1000
SET @d =SYSUTCDATETIME();
WITH tally(Nmbr) AS(SELECT TOP(@xml1000.value('count(/root/Subject)','int')) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM master..spt_values)
SELECT tally.Nmbr
,@xml1000.value('(/root/Subject[sql:column("tally.Nmbr")]/text())[1]','nvarchar(max)') AS [Subject]
,@xml1000.value('(/root/Score[sql:column("tally.Nmbr")]/text())[1]','nvarchar(max)') AS Score
INTO #t1000a
FROM tally;
SELECT 'xml1000 a',DATEDIFF(MILLISECOND,@d,SYSUTCDATETIME());
SET @d=SYSUTCDATETIME();
SELECT c.value('(./text())[1]', 'nvarchar(max)') AS [Subject]
, c.value('(/root/*[sql:column("w.r")]/text())[1]', 'nvarchar(max)') AS [Score]
INTO #t1000b
FROM @xml1000.nodes('/root/*[position() mod 2 = 1]') AS t(c)
CROSS APPLY (SELECT t.c.value('let $n := . return count(/root/*[. << $n[1]]) + 2','INT') AS r
) AS w;
SELECT 'xml1000 b',DATEDIFF(MILLISECOND,@d,SYSUTCDATETIME());
方法 a 是我使用計數的方法，方法 b 是 Yitzhak 使用 XQuery 的方法.
Method a is my approach using a tally, method b is Yitzhak's approach using XQuery.
這兩種方法的區別很小
10 Elements a=7ms / b=6ms
100 Elements a=83ms / b=79ms
1000 Elements a=8942ms / b=8721ms
一些一般差異:
- 計數方法也適用于每個系列的三元組或更多元素.
- Tally 方法仍然適用于中間的其他元素
- XQuery 方法可以更好地處理意外丟失的元素，但如果只丟失了一個預期元素，兩種方法都不會正確返回.
這篇關于如何從 XML 中提取值?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！
【網站聲明】本站部分內容來源于互聯網,旨在幫助大家更快的解決問題，如果有圖片或者內容侵犯了您的權益，請聯系我們刪除處理，感謝您的支持！