Re: SQLXML for xml explicit

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

In addition to Eugene's recommendation, I would also like to point out what
performance issues you have to compare against the alternative of doing the
XML-ification on the client:

Unless some of the XML that you need is static (such so-called wrapper
elements such as the root element or the orders element containing all order
elements), doing the joins and unions on the server normally is better since
it avoids multiple client-server roundtrips, joins can be optimized by the
server optimizer and less data may be transported between the server and the
client.

Best regards
Michael

"Eugene Kogan [MSFT]" <ekogan@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:uNnOPYQ2FHA.3596@xxxxxxxxxxxxxxxxxxxxxxx
> 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: 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)
  • setting dataset datarelation from database
    ... I want to set my dataset's datarelations based on the relationships ... I had a query I ... switched to using 'for xml explicit, ... If I run the query in sql query analyzer, I see the schema ...
    (microsoft.public.dotnet.framework.adonet)