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

I have the following SQL query, I am having trouble defining the form explicitly for

select tableName, uqName, col1, col2
from someTable

      

I would like to select the results in XML as shown below. I need col1 and col2 to appear as children and tableName and uqName to appear as attributes. If col1 or col2 null

then I need to specify the attribute IsNull

. Otherwise the value is fetched as the text node as a child of the Col element

One string returned from the above SQL would look like:

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

      

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

+3


source to share


1 answer


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>

      

+4


source







All Articles