locked
How can I show <FieldName></FieldName> INSTEAD OF <FieldName xsi:nil="true" /> in XML output? RRS feed

  • Question

  • I am trying to create an XML document as the result of queries against our database tables.

    In some cases I need fields (usually INT) to display as <FieldName xsi:nil ="true" /> and that seems to work just fine if I used "ELEMENTS XSINIL"

     

    However, I need to be able to chose (basically on a field by field basis in my SELECT statements) to return a blank field when the database holds a NULL value such as:

    <FieldName></FieldName>  (Or <FieldName/> either way would be fine).

     

    How can I do that?

    Thank you for your help!

     

    SELECT 
     RecordCode,
     FieldToBeBlank,
     [Year] ,
     [Month]
    FROM dbo.SomeTable
    FOR XML PATH('CustomRootName'), ELEMENTS XSINIL
    
    Friday, August 13, 2010 3:36 PM

Answers

  • OK, nevermind, I got it sorted:

     

     

     

    SELECT
    RecordCode,
    ISNULL( FieldToBeBlank, '') AS FieldToBeBlank,
    [Year] ,
    [Month]
    FROM dbo.SomeTable
    FOR XML PATH('CustomRootName'), ELEMENTS XSINIL

    • Marked as answer by Maxer_Ars Friday, August 13, 2010 5:02 PM
    Friday, August 13, 2010 5:02 PM