SELECT of Header and Items of an XML

Asked

Viewed 71 times

2

Hello,

How can I select one XML with header and items?

I have it:

DECLARE @XML XML = CONVERT(XML,'
<ROOT v="2.15.0">
  <LOTEANIH APP="00B1" LOTE="1">
    <LOTEANIM ANIMAL="10" />
    <LOTEANIM ANIMAL="11" />
    <LOTEANIM ANIMAL="12" />
    <LOTEANIM ANIMAL="13" />
    <LOTEANIM ANIMAL="14" />
  </LOTEANIH>
  <LOTEANIH APP="XB8F" LOTE="1">
    <LOTEANIM ANIMAL="1" />
    <LOTEANIM ANIMAL="2" />
    <LOTEANIM ANIMAL="3" />
    <LOTEANIM ANIMAL="4" />
    <LOTEANIM ANIMAL="5" />
  </LOTEANIH>
</ROOT>');

I’m selecting this way (I know it’s wrong :/):

WITH CTe AS (
    SELECT
         h.value('@APP', 'varchar(4)') AS APP_ID
        ,h.value('@LOTE', 'int') AS NRO_LOTE
        ,i.value('@ANIMAL', 'int') AS ANIMAL
    FROM @XML.nodes('/ROOT/LOTEANIH') AS header(h)
        CROSS APPLY @XML.nodes('/ROOT/LOTEANIH/LOTEANIM') AS child(i)
)
SELECT * FROM CTe

And I’m getting it back:
Retorno

While I search/need this return (separated equal to xml):
Correto

sql-server xml select

1 answer

1


I only changed the Xquery of @APP and of @LOTE. Everything else is almost the same.

DECLARE @XML XML = CONVERT(XML,'
<ROOT v="2.15.0">
  <LOTEANIH APP="00B1" LOTE="1">
    <LOTEANIM ANIMAL="10" />
    <LOTEANIM ANIMAL="11" />
    <LOTEANIM ANIMAL="12" />
    <LOTEANIM ANIMAL="13" />
    <LOTEANIM ANIMAL="14" />
</LOTEANIH>
<LOTEANIH APP="XB8F" LOTE="1">
    <LOTEANIM ANIMAL="1" />
    <LOTEANIM ANIMAL="2" />
    <LOTEANIM ANIMAL="3" />
    <LOTEANIM ANIMAL="4" />
    <LOTEANIM ANIMAL="5" />
</LOTEANIH>
</ROOT>');


select x.value('../@APP', 'VARCHAR(4)'),
       x.value('../@LOTE', 'INT'),
       x.value('./@ANIMAL', 'INT')
from @XML.nodes('/ROOT/LOTEANIH/LOTEANIM') h(x);

Browser other questions tagged sql-server xml select

You are not signed in. Login or sign up in order to post.