Exporting data from SQL to XML - NULL Value

Question

I exported data from SQL Server to XML file like this :

USE Northwind;

SELECT * FROM Products FOR XML PATH;

For NULL value , it returned xsi:nil="true".

For example: <ProductName xsi:nil="true"/>

Can I export without the xsi:nil="true" ?

But I do want to have tag name like : <ProductName /> or <ProductName></ProductName>

Can someone tell me how to export like this?

Thanks.

hi,what is the database that u using?

Low Chee Mun
– Low Chee Mun

2013-10-13 13:49:02 +00:00
Commented Oct 13, 2013 at 13:49 — Low Chee Mun
– Low Chee Mun, Commented Oct 13, 2013 at 13:49

tojnk · Accepted Answer · 2014-11-24 15:55:07Z

4

You can try something like this...

SELECT * FROM Products
    FOR XML RAW('customer'), ROOT('customers')

ROOT clause in for XML statement creates root element for your XML document to create a well formed XML document , IF you want all the values in Elements you can add the Elements Clause in your FOR XML statement, which would be something like this.

SELECT * FROM Products
FOR XML RAW('customer'), ROOT('customers'), Elements

Now if you add XSINIL clause at the end of it only then it will you the ELEMENTS will null values as , otherwise it will not return the element at all. Which will be something like this..

SELECT * FROM Products
FOR XML RAW('customer'), ROOT('customers'), Elements XSINIL

edited Nov 24, 2014 at 15:55

tojnk

53 bronze badges

answered Oct 13, 2013 at 15:36

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Exporting data from SQL to XML - NULL Value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related