January 20, 2009 at 3:52 am
Hello,
I am trying to return a xml string from a query using MSSQL 2000.
Something like this...
Select Names.Age, Names.LastName, Orders.Item
from Names, Orders
where Names.ClientID = Orders.ClientID
for xml auto
It will return something like:
Names Age="25" LastName="Moscon"
Orders Item ="01992"/
Orders Item ="01232"/
Orders Item ="04913"/
/Names
But if age is Null, it will return something like:
Names LastName="Moscon"
Orders Item ="01992"/
Orders Item ="01232/
Orders Item ="04913"/
/Names
Age attribute is missing!!!(why ??)
How can I always return all attributes, even it has null values?
January 20, 2009 at 3:59 am
The simple answer is to use ISNULL function around the field that could be NULL so that you return a value instead of NULL, e.g. empty string ('') or 0.
Si
January 20, 2009 at 4:35 am
Thanks Simon,
One more question, if i use the xml file generated using the above query and isnul function to insert data into a table will the null values in the xml file be inserted as null values in sql table?
January 20, 2009 at 5:34 am
NO. There is no need to insert any NULLS into the base tables. After all, they are already NULL which is why you are going to use ISNULL to get a value to shape the XML the way you want it.
The FOR XML AUTO statements are forming XML output from a relational table. The base tables will NOT be altered in any way by using ISNULL in a SELECT statement, only the output for the SELECT statement (and hence the XML output generated) will be changed.
Hope this helps.
Si
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply