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