SQl Server T-SQL XML with Optional Elements

Question

I have a table with a column of XML where some of the elements are optional. In this (extremely) simplified example, the code works when all elements are present, but I get nothing if one or more are absent. (i.e. zero rows instead of 1 with Y being null)

I arrived at this technique through various searches so there certainly might be a better approach. Ultimately, I want this to be a view (if that matters for the approach) but I should be able to figure that out.

declare @x XML = '<Root><A x="1"><B ySameName="2"/><C ySameName="3"/></A></Root>';
--declare @x XML = '<Root><A x="1"><C ySameName="3"/></A></Root>';
select EA.*, EB.*, EC.* from
(select c.node.value('@x', 'int') as X 
   from @x.nodes('//Root/A') AS c(node)) EA
,(select c.node.value('@ySameName', 'int') as YB 
   from @x.nodes('//Root/A/B') AS c(node)) EB
,(select c.node.value('@ySameName', 'int') as YC 
   from @x.nodes('//Root/A/C') AS c(node)) EC;

I can perform this as 3 selects using local vars, then return all the vars - but that seems bulky and I'm not sure how I'd do a view with that approach.

Thanks, D

Mikael Eriksson · Accepted Answer · 2012-07-29 18:08:09Z

1

Try this:

select T.N.value('@x', 'int') as EA,
       T.N.value('B[1]/@ySameName', 'int') as YB,
       T.N.value('C[1]/@ySameName', 'int') as YC
from @x.nodes('/Root/A') as T(N)

answered Jul 29, 2012 at 18:08

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

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

Collectives™ on Stack Overflow

SQl Server T-SQL XML with Optional Elements

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