Re: SQLXML for xml explicit

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" <Toby@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:64FA9411-ECB1-48BB-8A75-2931BC78E994@xxxxxxxxxxxxxxxx
> 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: FOR XML EXPLICIT - Empty Tags?
    ... You can do it by adding another tag (and therefore another UNION) to your ... SELECT 1 AS Tag, ... FOR XML EXPLICIT ... I need to output a query in a given XML format and I figure I'd have a stab ...
    (microsoft.public.sqlserver.xml)
  • 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)
  • Re: SQLXML for xml explicit
    ... Unless some of the XML that you need is static (such so-called wrapper ... > the query plan and the indexes are used by Query Processor to implement ... > UNION ALLs as MERGE UNIONs. ... >> in using for XML explicit and how do i overcome it.. ...
    (microsoft.public.sqlserver.xml)
  • RE: Query producing XML appears to be cached
    ... By default the XMLDataSource control always caches its data, ... I have an app that executed a sql server query that produces an XML ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Typed XML slows down query?!
    ... > Hi Martin, ... >>I have a performance problem with typed XML and a simple query. ... >> CREATE PRIMARY XML INDEX idx_article ...
    (microsoft.public.sqlserver.xml)
Loading