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






.

Relevant Pages

  • Re: Deviation from object-relational mapping (pySQLFace)
    ... Its goal to separate relational database stuff from algorythmic ... file (XML). ... It provides callable command objects for each sql query. ...
    (comp.lang.python)
  • Re: DISCOVER_XML_METADATA
    ... unprocessed cubes and it doesn't show processing status information about ... A DBSCHEMA_CATALOGS query will get you a list of all the databases ... the database list to the list of cubes for a selected database. ... The XML fresult is too long and I don't need eveything (e.g. dimension ...
    (microsoft.public.sqlserver.olap)
  • Re: DISCOVER_XML_METADATA
    ... Small XML is not problem. ... and then run following for each database: ... unprocessed cubes and it doesn't show processing status information about ... A DBSCHEMA_CATALOGS query will get you a list of all the databases ...
    (microsoft.public.sqlserver.olap)
  • Re: New Guy Educational References Sought
    ... VSTO to create a smart document. ... create the "form" in Word 2003, apply the XML schema to the document, ... word doc, or run the query against the database, store the result as an XML ...
    (microsoft.public.vsnet.vstools.office)
  • Re: DISCOVER_XML_METADATA
    ... Small XML is not problem. ... and then run following for each database: ... unprocessed cubes and it doesn't show processing status information ... A DBSCHEMA_CATALOGS query will get you a list of all the databases ...
    (microsoft.public.sqlserver.olap)