SQL select for xml explicit -- need help defining alternate structure when value is null

Question

I have the following SQL query that I'm having trouble explicitly defining the shape for

select tableName, uqName, col1, col2
from someTable

I would like to select the results into XML as below. I need the col1 and col2 to show up as children and tableName and uqName to show up as attributes. If col1 or col2 is null then I need to specify an IsNull attribute. Otherwise the value is selected as a text node as the child of the Col element

One row returned from the above SQL would look like this:

<UniqueKey Name="UniqueKeyName" TableName="TableName" >
  <Col Name="col1" IsNull="true" />
  <Col Name="col2">ABC</Col>
</UniqueKey>

How can I explicitly define this XML shape using SQL Server 2008 R2?

Also, do you want the XML as result of the SELECT or can you select into a buffer/file/pipe/etc. and then work on that to shape the result? — hroptatyr
– hroptatyr, Commented Mar 19, 2012 at 7:48
Ideally I could get this result directly as a result of the select — cordialgerm
– cordialgerm, Commented Mar 19, 2012 at 7:49

Mikael Eriksson · Accepted Answer · 2012-03-19 09:47:32Z

4

declare @T table
(
  tableName varchar(20),
  uqName varchar(20),
  col1 varchar(10),
  col2 varchar(10)
)

insert into @T values
('TableName', 'UniqueKeyName', null, 'ABC')
insert into @T values
('TableName', 'UniqueKeyName', null, null)
insert into @T values
('TableName', 'UniqueKeyName', '123', '456')

select uqName as "@Name",
       tableName as "@TableName",
       (select 'col1' as "@Name",
               case when col1 is null then 'true' end as "@IsNull",
               col1 as "*"
        for xml path('Col'), type),
       (select 'col2' as "@Name",
               case when col2 is null then 'true' end as "@IsNull",
               col2 as "*"
        for xml path('Col'), type)
from @T
for xml path('UniqueKey')

Result:

<UniqueKey Name="UniqueKeyName" TableName="TableName">
  <Col Name="col1" IsNull="true" />
  <Col Name="col2">ABC</Col>
</UniqueKey>
<UniqueKey Name="UniqueKeyName" TableName="TableName">
  <Col Name="col1" IsNull="true" />
  <Col Name="col2" IsNull="true" />
</UniqueKey>
<UniqueKey Name="UniqueKeyName" TableName="TableName">
  <Col Name="col1">123</Col>
  <Col Name="col2">456</Col>
</UniqueKey>

answered Mar 19, 2012 at 9:47

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

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

1 Comment

Indi Over a year ago

@RoyiNamir Yes agreed. I took mine off. +1

Collectives™ on Stack Overflow

SQL select for xml explicit -- need help defining alternate structure when value is null

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related