SQL Server XML - Multiple rows returned by for xml explicit

Author

Multiple rows returned by for xml explicit

roblenderman

2005-06-05, 3:59 pm

I have an xml query that runs across five tables. For some reasosn after I
get to the third table my xml returned is breaking into two rows in the
recordset that is returned. It just stops on the first record returned and
breaks mid element name to the second record. I have attached my query up
through table five. It breaks down on the last few closing elements and put
them on a second row. Not sure why this is happening and dealing with the
returned data is a pain. i have never seen for xml explicit returning the
xml as multiple rows before.

Rob

SELECT
1 AS Tag
,NULL AS Parent
,null as [catsubcatproduct!1!category]
,null as [category!2!catname!element]
,null as [category!2!catdesc!element]
,null as [category!2!subcategory!element]
,null as [subcategory!3!subcatname!element]
,null as [subcategory!3!subcatdesc!element]
,null as [subcategory!3!product!element]
,null as [product!4!productname!element]
,null as [product!4!productdesc!element]
,null as [product!4!company!element]
,null as [company!5!companyname!element]
,null as [company!5!companydesc!element]
union

SELECT
2 AS Tag
,1 AS Parent
,null as [catsubcatproduct!1!category]
,tcategory.catname as [category!2!catname!element]
,tcategory.catdesc as [category!2!catdesc!element]
,null as [category!2!subcategory!element]
,null as [subcategory!3!subcatname!element]
,null as [subcategory!3!subcatdesc!element]
,null as [subcategory!3!product!element]
,null as [product!4!productname!element]
,null as [product!4!productdesc!element]
,null as [product!4!company!element]
,null as [company!5!companyname!element]
,null as [company!5!companydesc!element]
from tcategory

--for xml explicit
union

SELECT
3 AS Tag
,2 AS Parent
,null as [catsubcatproduct!1!category]
,tcategory.catname as [category!2!catname!element]
,null as [category!2!catdesc!element]
,null as [category!2!subcategory!element]
,tsubcategory.subcatname as [subcategory!3!subcatname!element]
,tsubcategory.subcatdesc as [subcategory!3!subcatdesc!element]
,null as [subcategory!3!product!element]
,null as [product!4!productname!element]
,null as [product!4!productdesc!element]
,null as [product!4!company!element]
,null as [company!5!companyname!element]
,null as [company!5!companydesc!element]

from tcategory inner join tcatsubcat on tcategory.catname=tcatsubcat.catname
inner join
tsubcategory on tcatsubcat.subcatname=tsubcategory.subcatname

union

SELECT
4 AS Tag
,3 AS Parent
,null as [catsubcatproduct!1!category]
,tcategory.catname as [category!2!catname!element]
,null as [category!2!catdesc!element]
,null as [category!2!subcategory!element]
,tsubcategory.subcatname as [subcategory!3!subcatname!element]
,null as [subcategory!3!subcatdesc!element]
,null as [subcategory!3!product!element]
,tproduct.productname as [product!4!productname!element]
,tproduct.productdesc as [product!4!productdesc!element]
,null as [product!4!company!element]
,null as [company!5!companyname!element]
,null as [company!5!companydesc!element]

from tcategory inner join tcatsubcat on tcategory.catname=tcatsubcat.catname
inner join
tsubcategory on tcatsubcat.subcatname=tsubcategory.subcatname inner join
tsubcatproduct on tcatsubcat.subcatname=tsubcatproduct.subcatname inner join
tproduct on tsubcatproduct.productname=tproduct.productname

order by[category!2!catname!element],[subcateg
ory!3!subcatname!element]
for xml explicit

Michael Rys [MSFT]

2005-06-08, 4:02 am

I assume that you use the query analyzer of SQL Server 2000 to see the
result?

If so, that is the wrong tool to get reliable XML results. XML is streamed
back in rowset chunks over TDS. In order to see the data as the stream it
is, you either use the command stream object in ADO/OLEDB (or the equivalent
ADO.Net API), or - if you use ODBC - you need to concatenate the result
yourself.

QA basically shows the rowsets explicit and thus adds the linebreaks every
2034 or so characters...

Note that this will be much better dealt with in the SQL Server 2005
management studio. Hyperlinked XML opens an XML editor. Pretty

if I may
say so myself... :-)

Best regards
Michael

"roblenderman" <roblenderman@discussions.microsoft.com> wrote in message
news:73B244E1-6717-46A5-8C88-68A35AFAA66F@microsoft.com...
>I have an xml query that runs across five tables. For some reasosn after I
> get to the third table my xml returned is breaking into two rows in the
> recordset that is returned. It just stops on the first record returned
> and
> breaks mid element name to the second record. I have attached my query up
> through table five. It breaks down on the last few closing elements and
> put
> them on a second row. Not sure why this is happening and dealing with the
> returned data is a pain. i have never seen for xml explicit returning the
> xml as multiple rows before.
>
> Rob
>
>
>
> SELECT
> 1 AS Tag
> ,NULL AS Parent
> ,null as [catsubcatproduct!1!category]
> ,null as [category!2!catname!element]
> ,null as [category!2!catdesc!element]
> ,null as [category!2!subcategory!element]
> ,null as [subcategory!3!subcatname!element]
> ,null as [subcategory!3!subcatdesc!element]
> ,null as [subcategory!3!product!element]
> ,null as [product!4!productname!element]
> ,null as [product!4!productdesc!element]
> ,null as [product!4!company!element]
> ,null as [company!5!companyname!element]
> ,null as [company!5!companydesc!element]
> union
>
> SELECT
> 2 AS Tag
> ,1 AS Parent
> ,null as [catsubcatproduct!1!category]
> ,tcategory.catname as [category!2!catname!element]
> ,tcategory.catdesc as [category!2!catdesc!element]
> ,null as [category!2!subcategory!element]
> ,null as [subcategory!3!subcatname!element]
> ,null as [subcategory!3!subcatdesc!element]
> ,null as [subcategory!3!product!element]
> ,null as [product!4!productname!element]
> ,null as [product!4!productdesc!element]
> ,null as [product!4!company!element]
> ,null as [company!5!companyname!element]
> ,null as [company!5!companydesc!element]
> from tcategory
>
> --for xml explicit
> union
>
>
> SELECT
> 3 AS Tag
> ,2 AS Parent
> ,null as [catsubcatproduct!1!category]
> ,tcategory.catname as [category!2!catname!element]
> ,null as [category!2!catdesc!element]
> ,null as [category!2!subcategory!element]
> ,tsubcategory.subcatname as [subcategory!3!subcatname!element]
> ,tsubcategory.subcatdesc as [subcategory!3!subcatdesc!element]
> ,null as [subcategory!3!product!element]
> ,null as [product!4!productname!element]
> ,null as [product!4!productdesc!element]
> ,null as [product!4!company!element]
> ,null as [company!5!companyname!element]
> ,null as [company!5!companydesc!element]
>
> from tcategory inner join tcatsubcat on
> tcategory.catname=tcatsubcat.catname
> inner join
> tsubcategory on tcatsubcat.subcatname=tsubcategory.subcatname
>
>
> union
>
> SELECT
> 4 AS Tag
> ,3 AS Parent
> ,null as [catsubcatproduct!1!category]
> ,tcategory.catname as [category!2!catname!element]
> ,null as [category!2!catdesc!element]
> ,null as [category!2!subcategory!element]
> ,tsubcategory.subcatname as [subcategory!3!subcatname!element]
> ,null as [subcategory!3!subcatdesc!element]
> ,null as [subcategory!3!product!element]
> ,tproduct.productname as [product!4!productname!element]
> ,tproduct.productdesc as [product!4!productdesc!element]
> ,null as [product!4!company!element]
> ,null as [company!5!companyname!element]
> ,null as [company!5!companydesc!element]
>
> from tcategory inner join tcatsubcat on
> tcategory.catname=tcatsubcat.catname
> inner join
> tsubcategory on tcatsubcat.subcatname=tsubcategory.subcatname inner join
> tsubcatproduct on tcatsubcat.subcatname=tsubcatproduct.subcatname inner
> join
> tproduct on tsubcatproduct.productname=tproduct.productname
>
>
> order by[category!2!catname!element],[subcateg
ory!3!subcatname!element]
> for xml explicit

roblenderman

2005-06-10, 4:03 pm

You are right. I was using the sql reader and not the xmlreader. The sql
reader returns records and the xmlreader returns one xml. If you use the
sqlreader you can concat the records and it will work but it is a waste to do
it that way. Thanks

Rob

"Michael Rys [MSFT]" wrote:

> I assume that you use the query analyzer of SQL Server 2000 to see the
> result?
>
> If so, that is the wrong tool to get reliable XML results. XML is streamed
> back in rowset chunks over TDS. In order to see the data as the stream it
> is, you either use the command stream object in ADO/OLEDB (or the equivalent
> ADO.Net API), or - if you use ODBC - you need to concatenate the result
> yourself.
>
> QA basically shows the rowsets explicit and thus adds the linebreaks every
> 2034 or so characters...
>
> Note that this will be much better dealt with in the SQL Server 2005
> management studio. Hyperlinked XML opens an XML editor. Pretty

if I may
> say so myself... :-)
>
> Best regards
> Michael
>
> "roblenderman" <roblenderman@discussions.microsoft.com> wrote in message
> news:73B244E1-6717-46A5-8C88-68A35AFAA66F@microsoft.com...
>
>
>

Sponsored Links