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
cordialgerm
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
Mikael Eriksson
source
to share