SQLXML for xml explicit

Hi ppl,
I am a newbie,a rookie in to the database world.I've got my first job now
in a small company who couldnt offord a full fledged DBA.
We have a requirement in which we need to query a lot of tables and form
numerous XML with those data.
I succeeded in completing the query for sample format by using for xml
explicit ( sql server 2000).
i had to make three select statements and union all to create the universal
table.

Well i was proud of myself.untill the client called.
He dropped the bomb shell that we need to generate a big XML with lots of
child parent.... and on seeing the query that i've written started talking
abt the performance issues..... and has asked me to prepare a doc to give
the various options available and analysing each and choosing the best.

so My question to all you ppl is that what performance issues would i face
in using for XML explicit and how do i overcome it..

is there someother efficent way to get XMl out of the database...


here is the query that i wrote..( i may be needed to have 7 selects and say
10 or more Xmls are to be generated )

SELECT
1 AS tag,
0 AS parent,
y.coursetype AS [X!1!type],
x.coursetitle AS [X!1!coursetitle],
x.courseid AS [X!1!courseid],
y.curriculumid AS [X!1!curriculumid],
y.coursehelptext AS [X!1!helptext],
y.mastery AS [X!1!mastery],
null AS [Y!2!type],
null AS [Y!2!name],
null AS [Y!2!helptext],
null AS [Z!3!type],
null AS [Z!3!name],
null AS [Z!3!location],
null AS [Z!3!active],
null AS [Z!3!duration],
null AS [Z!3!objective],
null AS [Y!2!lessonid!hide],
null AS [Z!3!topicid!hide]
FROM
X x,
Y y

WHERE
x.courseid=y.courseid

UNION ALL

SELECT
2,
1,
y.coursetype,
x.coursetitle ,
x.courseid,
y.curriculumid,
y.coursehelptext,
y.mastery,
z.lessontype,
z.lessonname ,
z.lessonhelptext,
null,
null,
null,
null,
null,
null,
z.lessonid,
null

FROM
X x,
Y y,
Z z

WHERE
x.courseid = y.courseid AND
z.courseid = x.courseid


UNION ALL

SELECT
3,
2,
y.coursetype,
x.coursetitle ,
x.courseid,
y.curriculumid,
y.coursehelptext,
y.mastery,
z.lessontype ,
z.lessonname ,
z.lessonhelptext ,
a.topicquiztype,
a.topicquizname,
a.quizlocation,
a.active,
a.topicquizobjective,
a.topicquizduration,
z.lessonid,
a.topicquizid


FROM
X x,
Y y,
Z z,
A a

WHERE
x.courseid = y.courseid AND
z.courseid = x.courseid AND
a.courseid = x.courseid AND
a.lessonid = z.lessonid


ORDER BY [X!1!courseid],[Y!node!2!lessonid!hide],[Z!3!topicid!hide]

FOR XML EXPLICIT

waiting eagerly ( scratching my head),
toby

To make a FOR XML EXPLICIT query to perform the best you could create enough
indexes on the tables so that ORDER BY does not result in a SORT in the
query plan and the indexes are used by Query Processor to implement UNION
ALLs as MERGE UNIONs.
As you can see this is not a FOR XML specific advice and the general
indexing trade-off between query performance, DML performance and data size
should be applied.

Best regards,
Eugene
---
This posting is provided "AS IS" with no warranties, and confers no rights.

"Toby" wrote in message

> Hi ppl,
> I am a newbie,a rookie in to the database world.I've got my first job now
> in a small company who couldnt offord a full fledged DBA.
> We have a requirement in which we need to query a lot of tables and form
> numerous XML with those data.
> I succeeded in completing the query for sample format by using for xml
> explicit ( sql server 2000).
> i had to make three select statements and union all to create the
> universal
> table.
>
> Well i was proud of myself.untill the client called.
> He dropped the bomb shell that we need to generate a big XML with lots of
> child parent.... and on seeing the query that i've written started talking
> abt the performance issues..... and has asked me to prepare a doc to give
> the various options available and analysing each and choosing the best.
>
> so My question to all you ppl is that what performance issues would i face
> in using for XML explicit and how do i overcome it..
>
> is there someother efficent way to get XMl out of the database...
>
>
> here is the query that i wrote..( i may be needed to have 7 selects and
> say
> 10 or more Xmls are to be generated )
>
> SELECT
> 1 AS tag,
> 0 AS parent,
> y.coursetype AS [X!1!type],
> x.coursetitle AS [X!1!coursetitle],
> x.courseid AS [X!1!courseid],
> y.curriculumid AS [X!1!curriculumid],
> y.coursehelptext AS [X!1!helptext],
> y.mastery AS [X!1!mastery],
> null AS [Y!2!type],
> null AS [Y!2!name],
> null AS [Y!2!helptext],
> null AS [Z!3!type],
> null AS [Z!3!name],
> null AS [Z!3!location],
> null AS [Z!3!active],
> null AS [Z!3!duration],
> null AS [Z!3!objective],
> null AS [Y!2!lessonid!hide],
> null AS [Z!3!topicid!hide]
> FROM
> X x,
> Y y
>
> WHERE
> x.courseid=y.courseid
>
> UNION ALL
>
> SELECT
> 2,
> 1,
> y.coursetype,
> x.coursetitle ,
> x.courseid,
> y.curriculumid,
> y.coursehelptext,
> y.mastery,
> z.lessontype,
> z.lessonname ,
> z.lessonhelptext,
> null,
> null,
> null,
> null,
> null,
> null,
> z.lessonid,
> null
>
> FROM
> X x,
> Y y,
> Z z
>
> WHERE
> x.courseid = y.courseid AND
> z.courseid = x.courseid
>
>
> UNION ALL
>
> SELECT
> 3,
> 2,
> y.coursetype,
> x.coursetitle ,
> x.courseid,
> y.curriculumid,
> y.coursehelptext,
> y.mastery,
> z.lessontype ,
> z.lessonname ,
> z.lessonhelptext ,
> a.topicquiztype,
> a.topicquizname,
> a.quizlocation,
> a.active,
> a.topicquizobjective,
> a.topicquizduration,
> z.lessonid,
> a.topicquizid
>
>
> FROM
> X x,
> Y y,
> Z z,
> A a
>
> WHERE
> x.courseid = y.courseid AND
> z.courseid = x.courseid AND
> a.courseid = x.courseid AND
> a.lessonid = z.lessonid
>
>
> ORDER BY [X!1!courseid],[Y!node!2!lessonid!hide],[Z!3!topicid!hide]
>
> FOR XML EXPLICIT
>
> waiting eagerly ( scratching my head),
> toby
>
>
>
>
>
>