Come estrarre il valore da XML? [chiuso]

Question

Ho la variabile XML definita di seguito e il suo valore. Per favore aiuto

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

Il risultato atteso dovrebbe essere:

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

Answer 1

Un'altra soluzione per un numero illimitato di coppie di elementi <Subject>e <Score>.

Mostra la potenza dell'espressione T-SQL e XQuery FLWOR.

Il metodo n. 1 è un processo in due fasi:

(1) Trasforma XML nel seguente formato:

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

(2) Distruggi in formato rettangolare / relazionale

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

Produzione

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

Applichiamo la stessa tecnica, ma senza trasformazione CTE e XML. Diventa molto più breve e più performante.

Metodo n. 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 un altro approccio, che dovrebbe essere un po 'più veloce ...

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;

L'idea in breve:

• Creiamo un conteggio al volo utilizzando una clausola TOP calcolata insieme a ROW_NUMBER()qualsiasi tabella con un conteggio di righe più grande (io uso master..spt_values ​​qui, la cosa migliore era una tabella di numeri fisici ...)
• Ora possiamo prendere ogni valore dalla sua posizione usando sql:column()per ottenere il valore corrente del conteggio in XQuery.
• Ciò significa: scegliamo il primo soggetto con la prima partitura. Poi il secondo Soggetto con il secondo punteggio e così via ...

Suggerimento: questo formato è molto errato. Se questo è sotto il tuo controllo, dovresti davvero cambiarlo. Ti affidi completamente all'ordine e alla posizione dell'elemento. Un elemento mancante o qualsiasi confusione o altri elementi intermedi potrebbe farlo crollare.

Userei qualcosa di simile

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

o

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

UPDATE Benchmark

Quanto segue confronterà un XML con 10/100/1000 coppie nella struttura pari / dispari:

- Assicurati di utilizzare un database, in cui questa tabella restituisce almeno 1000 righe (o usa qualsiasi altra tabella)

SELECT COUNT(*) FROM master..spt_values

- Riempimento di una tabella con dati fittizi

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;

- utilizzando tre XML con diverso numero di coppie

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

- prova per 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());

-test per 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());

-test per 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());

Il metodo a è il mio approccio che utilizza un conteggio, il metodo b è l'approccio di Yitzhak che utilizza XQuery.

La differenza tra questi due approcci è piuttosto piccola

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

Alcune differenze generali:

• L'approccio conteggio funzionerebbe anche con triple o più elementi per serie.
• L'approccio conteggio funzionerebbe ancora con altri elementi intermedi
• l'approccio XQuery gestirà meglio gli elementi inaspettatamente mancanti, ma entrambi gli approcci non tornerebbero correttamente, se mancasse solo un elemento previsto.

Answer 3

Senza un collegamento tra il <Subject>e il <Score>tag, potresti provare questo. Il numero di riga generato come collegamento tra entrambi i tag si basa sul motore SQL per restituire le righe nell'ordine corretto.

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