CODEWITHSUNDEEP
sql-serverxmlt-sqlxpathxquery
TurboLa5
TurboLa5

Reputation: 1

MSSQL Parsing of XML that Contains same Sibling

I am trying to query a MSSQL database that has XML and parse it out.

here is the data:

<Root>
<Relatives>
<Relative>
  <Relation>Father</Relation>
  <BcAge>50</BcAge>
  <BilatAge>0</BilatAge>
  <OcAge>0</OcAge>
</Relative>
<Relative>
  <Relation>Mother</Relation>
  <BcAge>58</BcAge>
  <BilatAge>0</BilatAge>
  <OcAge>0</OcAge>
</Relative>

Here is the desired result

Relation BcAge BilatAge OcAge Relation BcAge BilatAge OcAge 
Father   50     0       0      Mother  58    0         0

Upvotes: 0

Views: 60

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

I doubt, that you really want to get your result side-by-side... This is no valid resultset, your column names are duplicated.

If you really need it this way you might do this:

DECLARE @xml XML=
N'<Root>
<Relatives>
<Relative>
  <Relation>Father</Relation>
  <BcAge>50</BcAge>
  <BilatAge>0</BilatAge>
  <OcAge>0</OcAge>
</Relative>
<Relative>
  <Relation>Mother</Relation>
  <BcAge>58</BcAge>
  <BilatAge>0</BilatAge>
  <OcAge>0</OcAge>
</Relative>
</Relatives>
</Root>';

The query will use a CTE to get the Father and the Mother with an XQuery-predicate

WITH Parents AS
(
    SELECT @xml.query(N'/Root/Relatives/Relative[(Relation/text())[1]="Father"]') AS Father
          ,@xml.query(N'/Root/Relatives/Relative[(Relation/text())[1]="Mother"]') AS Mother
)
SELECT 'Father' AS F_Relation
      ,Father.value(N'(/Relative/BcAge)[1]',N'int') AS F_BcAge
      ,Father.value(N'(/Relative/BilatAge)[1]',N'int') AS F_BilatAge
      ,Father.value(N'(/Relative/OcAge)[1]',N'int') AS F_OcAge
      ,'Mother' AS M_Relation   
      ,Mother.value(N'(/Relative/BcAge)[1]',N'int') AS M_BcAge
      ,Mother.value(N'(/Relative/BilatAge)[1]',N'int') AS M_BilatAge
      ,Mother.value(N'(/Relative/OcAge)[1]',N'int') AS M_OcAge
FROM Parents;

But probably it's this you are looking for (any count of <Relative>):

SELECT rel.value(N'(Relation/text())[1]',N'nvarchar(max)') AS Relation
      ,rel.value(N'(BcAge/text())[1]',N'int') AS BcAge
      ,rel.value(N'(BilatAge/text())[1]',N'int') AS BilatAge
      ,rel.value(N'(OcAge/text())[1]',N'int') AS OcAge
FROM @xml.nodes(N'/Root/Relatives/Relative') AS A(rel)

Upvotes: 1

Related Questions