have elements even when column has NULL value

Hi guys,
Sorry if posted twice as I could not see the previous post...
I need to capture element name even when the column it is associate to has a NULL value. I can best describe this with an example...
Say for example we have 2 tables names tblProduct and tblCategory with following columns and relation...
tblProduct
ProductId int (PK)
ProductName varchar(30)
CategoryId int (FK to tblCategory)
tblCategory
CategoryId (PK)
CategoryName varchar(30)
table data...
tblProduct
ProductId ProductName CategoryId
123 testProduct1 777
345 testProduct2 NULL
678 testProdyct3 888
tblCategory
CategoruId CategoryName
777 testCategory1
888 testCategory9
999 testCategory44
Now I need to have XML output as...
<Products><Product><ProductId>123</ProductId><ProductName>testProduct1</ProductName><CategoryName>testCategory1</CategoryName></Product><Product><ProductId>345</ProductId><ProductName>testProduct2</ProductName><CategoryName></CategoryName></Product><Produ
ct>
...
...
</Product></Products>
In the above case, CategoryName element should be captured even when the product has null value for it.
So, you have an schema xsd as...
<?xml version="1.0" encoding="UTF-8"?><xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"><xsd:annotation><xsd:appinfo><sql:relation ship name="ProductCategory"
parent="tblProduct"
parent-key="CategoryId"
child="tblCategory"
child-key="CategoryId" /></xsd:appinfo></xsd:annotation><xsd:element name="Products" sql:is-constant="1"><xsd:complexType><xsd:sequence maxOccurs="unbounded"><xsd:element ref="Product"/></xsd:sequence></xsd:complexType></xsd:element><xsd:element name="P
roduct" type="ProductDetails"
sql:relation="tblProduct"
sql:key-field="ProductId"/><xsd:complexType name="ProductDetails"><xsd:sequence><xsd:element name="ProductId"
type="xsd:integer"
sql:field="ProductId"/><xsd:element name="ProductName"
type="xsd:string"
sql:field="ProductName"/><xsd:element name="CategoryName"
type="xsd:string"
sql:field="CategoryName"
sql:relation="tblCategory"
sql:relationship="ProductCategory"/></xsd:sequence></xsd:complexType></xsd:schema>
The problem is that you get below output when CategoryId is null for some of the products
<Products><Product><ProductId>123</ProductId><ProductName>testProduct1</ProductName><CategoryName>testCategory1</CategoryName></Product><Product><ProductId>345</ProductId><ProductName>testProduct2</ProductName>
***** error ***** does not get captured
</Product><Product>
...
...
</Product></Products>
I would apprecaite if anyone has a solution for this.
Thanks
-Sid
"sid" <anonymous@.discussions.microsoft.com> wrote in message
news:FBC3236C-9C0E-49E1-971D-4E7FD4F638DF@.microsoft.com...
> Hi guys,
> Sorry if posted twice as I could not see the previous post...
> I need to capture element name even when the column it is associate to has
> a NULL value. I can best describe this with an example...
See this FAQ:
http://sqlxml.org/faqs.aspx?faq=14
Bryant

Handling world time with SQL and ASP.NET

Hi,

I would like to capture date time in my SQL during a certain operation. This piece of data will be displayed to an ASP.NET page.

When displaying the date time, I want it converted to the local time zone equivalent as set in the user's operating system.

What setup do I need on the SQL and the coding in the ASP.NET page?

Thanks.The following MSDN article may help you to understand date & time and also avoid inherant problems when dealing with different time zones.

Best Practices Using DateTime in the .NET

Hope this helps

Regards

Wayne Phipps|||Hi Wayne,

Thanks. That's a great article on issues related to saving and computing date time when daylight saving is relevant.

I'm also looking for saving date time in one time zone, and then display the date time relevant to the target user's system. I managed to find a web site that's dedicated to addressing this issue, SimpleTimeZone .NET Framework Class (http://www.michaelbrumm.com/simpletimezone.html). However I have not evaluated the .NET class concerned.

Thanks again for your reply.