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

Question

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

Example 1 ( 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 ( 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

Tomalak · Accepted Answer · 2018-08-20 09:53:27Z

3

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

edited Aug 20, 2018 at 9:53

answered Aug 20, 2018 at 9:34

Tomalak

339k68 gold badges547 silver badges635 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related