CODEWITHSUNDEEP
sqlsql-serverxmlt-sql
AG7
AG7

Reputation: 77

T-SQL - select XML node values, or NULL if the nodes don't exist

I have an XML that looks like below. As of now if both A and B nodes are coming in the XML, I can easily map their attribute i.e. type="A-type" and type="B-type".

I'm stuck at a scenario when <B> node may not be present in the XML and then too, I need to get <A> node attribute i.e. type="A-type", but current logic returns me no rows. Please help how to achieve this.

Please note that <B> can come any number of times under <A>, that's why we have to use the path Root/A/B. Since occurrence of <B> is not predicted, I can't change the path.

Example 1 (<B> is present):

DECLARE @myXML XML = 
'<Root>
<A type="A-type">
<B type="B-type"></B>
</A>
</Root>'

SELECT 
  N.value('(../@type)[1]','VARCHAR(100)'), 
  N.value('(@type)[1]','VARCHAR(100)') 
FROM @myXML.nodes('Root/A/B') AS X(N)

Result Example 1

Example 2 (<B> is not present):

DECLARE @myXML XML = 
'<Root>
<A type="A-type">
</A>
</Root>'

SELECT 
  N.value('(../@type)[1]','VARCHAR(100)'), 
  N.value('(@type)[1]','VARCHAR(100)') 
FROM @myXML.nodes('Root/A/B') AS X(N)

Result Example 2

Expected Output:

Expected Result

Upvotes: 2

Views: 574

Answers (1)

Tomalak
Tomalak

Reputation: 338376

Try this

SELECT 
  x1.A.value('@type','VARCHAR(100)'), 
  x2.B.value('@type','VARCHAR(100)')
FROM
  @myXML.nodes('/Root/A[1]') AS x1(A)
  OUTER APPLY @myXML.nodes('/Root/A[1]/B') AS x2(B)

With

<Root>
  <A type="A-type">
    <B type="B-type 1"></B>
    <B type="B-type 2"></B>
    <B type="B-type 3"></B>
  </A>
</Root>

it returns 

A-type  B-type 1
A-type  B-type 2
A-type  B-type 3

with 

<Root>
  <A type="A-type">
    <B type="B-type"></B>
  </A>
</Root>

it returns

A-type  B-type

and with 

<Root>
  <A type="A-type">
  </A>
</Root>

it returns

A-type  NULL

Upvotes: 3

Related Questions