I am running a query from SQL Server 2008 R2 using FOR XML PATH. My only issue is that I want ALL elements to appear, even if they are NULL and I want empty (or null) elements to return as
<MyElement />
Not as
<MyElement></MyElement>
-
(Thanks for editing marc_s. Looks much better.)J Brun– J Brun2011-12-02 16:37:33 +00:00Commented Dec 2, 2011 at 16:37
-
1Those are technically equivalent according to the XML spec. "The representation of an empty element is either a start-tag immediately followed by an end-tag, or an empty-element tag." w3.org/TR/REC-xml/#NT-contentRiley Major– Riley Major2015-07-28 17:53:25 +00:00Commented Jul 28, 2015 at 17:53
Add a comment
|
2 Answers
You can query the field in a sub-query in the filed list, using for xml, create both versions of empty element.
declare @T table
(
ID int identity primary key,
Name nvarchar(10)
)
insert into @T(Name)
select 'Name 1' union all
select null union all
select 'Name 2'
select ID,
(select Name as '*' for xml path(''), type) as Name,
(select Name as '*' for xml path('Name'), type)
from @T
for xml path('row')
Result:
<row>
<ID>1</ID>
<Name>Name 1</Name>
<Name>Name 1</Name>
</row>
<row>
<ID>2</ID>
<Name></Name>
<Name />
</row>
<row>
<ID>3</ID>
<Name>Name 2</Name>
<Name>Name 2</Name>
</row>
Comments
Have look at following Example
DECLARE @t TABLE (
id INT, Name1 VARCHAR(20),
Value1 VARCHAR(20), Name2 VARCHAR(20),
Value2 VARCHAR(20))
INSERT INTO @t (id, name1, value1, name2, value2)
SELECT 1, 'PrimaryID', NULL, 'LastName', 'Abiola' UNION ALL
SELECT 2, 'PrimaryID', '200', 'LastName', 'Aboud'
SELECT
(
SELECT
name1 AS 'Parameter/Name',
value1 AS 'Parameter/Value'
FROM @t t2 WHERE t2.id = t.id
FOR XML PATH(''), ELEMENTS XSINIL, TYPE
),
(
SELECT
name2 AS 'Parameter/Name',
value2 AS 'Parameter/Value'
FROM @t t2 WHERE t2.id = t.id
FOR XML PATH(''), TYPE
)
FROM @t t
FOR XML PATH('T2Method')
You will get output like following
<T2Method>
<Parameter xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Name>PrimaryID</Name>
<Value xsi:nil="true" />
</Parameter>
<Parameter>
<Name>LastName</Name>
<Value>Abiola</Value>
</Parameter>
</T2Method>
<T2Method>
<Parameter xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Name>PrimaryID</Name>
<Value>200</Value>
</Parameter>
<Parameter>
<Name>LastName</Name>
<Value>Aboud</Value>
</Parameter>
</T2Method>
Note the "value" element in the first "paremeter" element. The value is NULL and still an element is generated. Note the addition of a special attribute "xsi:nil" to indicate that the element is empty.
3 Comments
J Brun
Thank you, I realize I can do that, and go back in and remove the 'xsi:nil' from my data, but that's an extra step I'd like to avoid if I could.
gngolakia
Well I am searching it. And if you found let me know please. Thanks
Qpirate
Thanks for this, i was banging my head against the wall with something like this.