FOR XML PATH , for NULL column values

• sankyverma38

  Old Hand

  Points: 350

  More actions

  May 21, 2010 at 3:41 am

  #219189

  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

• sankyverma38

  Old Hand

  Points: 350

  More actions

  May 28, 2010 at 12:20 pm

  #1174091

  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

• jbuys_54

  Old Hand

  Points: 363

  More actions

  June 12, 2012 at 7:04 am

  #1499769

  Brilliant.

  Is there a way to do the same with the explicit mode?