问题描述
我有下面定义的 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 方法可以更好地处理意外丢失的元素,但如果只丢失了一个预期元素,两种方法都不会正确返回.