Como extrair o valor do XML? [fechadas]

Question

Tenho a variável XML definida abaixo e seu valor. Por favor ajude

DECLARE @xml2 as XML ;                          
SET @xml2 = '<Student>
  <Marks>
    <Subject>Science</Subject>
    <Score>89</Score>
    <Subject>Maths</Subject>
    <Score>90</Score>
  </Marks>
</Student>'

O resultado esperado deve ser:

Subject  Score
-------- ------
Science  89
Maths    90

Answer 1

Outra solução para um número ilimitado de pares de elementos <Subject>e <Score>.

Ele mostra o poder da expressão T-SQL e XQuery FLWOR.

O Método # 1 é um processo de duas etapas:

(1) Transforme o XML no seguinte formato:

<root>
  <r subject="Science" score="89" />
  <r subject="Maths" score="90" />
  ...
</root>

(2) Fragmentar em formato retangular / relacional

SQL

DECLARE @xml as XML = 
N'<Student>
  <Marks>
    <Subject>Science</Subject>
    <Score>89</Score>
    <Subject>Maths</Subject>
    <Score>90</Score>
    <Subject>History</Subject>
    <Score>100</Score>
  </Marks>
</Student>';

;WITH rs AS
(
    SELECT @xml.query('<root>
    {
        for $x in /Student/Marks/*[position() mod 2 = 1] let $pos := count(/Student/Marks/*[. << $x[1]]) + 1 return <r subject="{$x/text()}" score="{/Student/Marks/*[$pos + 1]}"/>
    }
    </root>') AS xmldata
)
SELECT c.value('@subject', 'VARCHAR(30)') AS [Subject]
    , c.value('@score', 'INT') AS [Score]
FROM rs CROSS APPLY xmldata.nodes('/root/r') AS t(c);

Resultado

+---------+-------+
| Subject | Score |
+---------+-------+
| Science |    89 |
| Maths   |    90 |
| History |   100 |
+---------+-------+

Vamos aplicar a mesma técnica, mas sem transformação CTE e XML. Torna-se muito mais curto e com mais desempenho.

Método # 2

SELECT c.value('(./text())[1]', 'VARCHAR(30)') AS [Subject]
    , c.value('(/Student/Marks/*[sql:column("w.r")]/text())[1]', 'INT') AS [Score]
FROM @xml.nodes('/Student/Marks/*[position() mod 2 = 1]') AS t(c)
    CROSS APPLY (SELECT t.c.value('let $n := . return count(/Student/Marks/*[. << $n[1]]) + 2','INT') AS r
         ) AS w;

Answer 2

E mais uma abordagem, que deve ser um pouco mais rápida ...

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;

A ideia em resumo:

• Criamos uma contagem em tempo real usando uma cláusula TOP computada junto com ROW_NUMBER()contra qualquer tabela com uma contagem de linhas maior (eu uso master..spt_values ​​aqui, melhor era uma tabela de números físicos ...)
• Agora podemos pegar cada valor por sua posição usando sql:column()para obter o valor atual da contagem na XQuery.
• Isso significa: Escolhemos o primeiro Assunto com a primeira Pontuação. Do que o segundo Sujeito com a segunda pontuação e assim por diante ...

Dica: este formato é muito errôneo. Se isso estiver sob seu controle, você realmente deve alterá-lo. Você está confiando totalmente na ordem e na posição do elemento. Um elemento ausente ou qualquer confusão ou outros elementos entre eles podem derrubar isso no chão.

Eu usaria algo como

<Student>
  <Marks Subject="Science" Score="80"/>
  <Marks Subject="Maths" Score="90"/>
</Student>

ou

<Student>
  <Marks>
    <Subject name="Science">80</Subject>
    <Subject name="Maths">90</Subject>
  </Marks>
</Student>

UPDATE Benchmark

O seguinte irá comparar um XML com pares 10/100/1000 em estrutura ímpar / par:

- Certifique-se de usar um banco de dados, onde esta tabela retorna pelo menos 1000 linhas (ou use qualquer outra tabela)

SELECT COUNT(*) FROM master..spt_values

- Preenchendo uma tabela com dados fictícios

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;

- usando três XMLs com contagem diferente de pares

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')
);

--teste para 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());

--teste para 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());

--teste para 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());

O método a é minha abordagem usando um registro, o método b é a abordagem de Yitzhak usando XQuery.

A diferença entre essas duas abordagens é bastante pequena

  10 Elements a=7ms     / b=6ms
 100 Elements a=83ms    / b=79ms
1000 Elements a=8942ms  / b=8721ms

Algumas diferenças gerais:

• A abordagem de contagem também funcionaria com triplos ou mais elementos por série.
• A abordagem de contagem ainda funcionaria com outros elementos entre
• a abordagem XQuery lidaria melhor com elementos ausentes inesperadamente, mas ambas as abordagens não retornariam corretamente se apenas um elemento esperado estivesse ausente.

Answer 3

Sem um link entre o <Subject>e a <Score>tag, você pode tentar isso. O número da linha que é gerado como um link entre as duas tags depende do mecanismo SQL para retornar as linhas na ordem adequada.

with cte_sub as
(
  select row_number() over(order by x.Sub) as Num,
         x.Sub.value('.', 'nvarchar(10)') as Subject
  from @xml2.nodes('/Student/Marks/Subject') as x(Sub)
),
cte_sco as
(
  select row_number() over(order by y.Sco) as Num,
         y.Sco.value('.', 'int') as Score
  from @xml2.nodes('/Student/Marks/Score') as y(Sco)
)
select c1.Subject, c2.Score
from cte_sub c1
join cte_sco c2
  on c2.Num = c1.Num;

Violino