CODEWITHSUNDEEP
sqlxmlsql-server-2008
cordialgerm
cordialgerm

Reputation: 8503

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

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?

Upvotes: 3

Views: 527

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

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>

Upvotes: 4

Related Questions