Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

FOR XML PATH , for NULL column values Expand / Collapse
Author
Message
Posted Friday, May 21, 2010 3:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, February 23, 2014 11:15 PM
Points: 36, Visits: 59
Hi All,

I need to create xml from a table. I am using FOR XML in PATH MODE.
But for columns having null value , the node doesn't get created.

I can use ELEMENT XSINIL , but I donot want xsi:nill = true and the namespace to appear in the xml.
As in the below xml :

<Address xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Id>63</Id>
<AccountId>80</AccountId>
<ContactId xsi:nil="true" />
<State xsi:nil="true" />
<ZipCode xsi:nil="true" />
<Region xsi:nil="true" />
<Country>BELGIUM</Country>
</Address>
Please suggest , any other way it is possible to create

<Address>
<Id>63</Id>
<AccountId>80</AccountId>
<ContactId />
<State />
<ZipCode />
<Region />
<Country>BELGIUM</Country>
</Address>

Please suggest
Post #925789
Posted Friday, May 28, 2010 12:20 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, February 23, 2014 11:15 PM
Points: 36, Visits: 59
This can be done be using ISNULL() check.

SELECT
ISNULL(column_name1,'') AS "ColumnName1",
ISNULL(column_name2,'') AS "ColumnName2"
ISNULL(column_name3,'') AS "ColumnName3"
FROM table FOR XML PATH('Table'), ELEMENTS



Post #929872
Posted Tuesday, June 12, 2012 7:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 1:50 AM
Points: 239, Visits: 74
Brilliant.

Is there a way to do the same with the explicit mode?
Post #1314408
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse