SQL Server Performance

xml production with default namespaces and subqueries

Discussion in 'SQL Server 2005 General Developer Questions' started by lucutus, Jan 21, 2008.

  1. lucutus New Member

    Hello all,
    I recently started a project to produce xml data from relational data with default namespaces. I use the new path method.
    An abstract example:

    ;WITH XMLNAMESPACES(DEFAULT 'http://booking.domain.info/ns1')
    SELECT UID AS "@ID", Name AS "@Name"
    FOR XML Path('Listing')
    ... well, the xml comes out with a default namespace in the Listing Element, but when I introduce a subquery like
    ;WITH XMLNAMESPACES(DEFAULT 'http://booking.domain.info/ns1')
    SELECT UID AS "@ID", Name AS "@Name",
    (SELECT CityID AS "@CityID", City AS "@City" FOR XML Path('City'), Type)
    FOR XML Path('Listing')
    ... now the City element has in its "attributes" the default namespace 'http://booking.domain.info/ns1'
    1. Question: How can I avoid this? I tried to save each subquery beforehand in a variable of type xml, and this works well, but what about performance in production? Is there a good alternative to the WITH XMLNAMESPACES clausel?
    2. Question: Does it really harm?
    Thanx beforehand!

  2. Luis Martin Moderator

    Unfortunately no one answered this question.
    This one is to remove from answered because is the post is to old.
    Moderators, free feel to change or delete my action.
  3. lucutus New Member

    Its disappointing that nobody had any thoughts about this.
    Here are two ways to get rid of the additional (default) namespaces:
    Look at this xml
    <Listing xmlns="http://sql.listing" ID="1">
    <Address xmlns="http://sql.address" Type="1">
    <Firstname> Gordon </Firstname>
    <Lastname> Freeman </Lastname>
    <Geo xmlns="http://sql.geo" Nord="46.115678" East="11.102345" />
    <Payment xmlns="http://sql.payment" />
    <Option ID="1"> Visa, Mastercard </Option>
    To exactly reproduce this xml you may want to write a for xml path('listing') query, but you can declare only one default namespace finding yourself to use prefixes. Definitly a solution, but here we want to produce exactly this xml style.

    1. You can declare several xml variables for @address, @geo, @payment. Assuming that you can join all needed data over the Listing ID, first get your @Listing Value. In this case 1.
    Then you write a query for each namespace using the with xmlnamespaces (default 'http://sql.namespace') option and the for xml path('element'), type variant. Save the results in each variable.
    Finally, compose the xml structure as follow:
    ;WITH XMLNAMESPACES (DEFAULT "http://sql.listing")
    SELECT @ListingID AS "@ID",
    (SELECT @Address),
    (SELECT @Geo),

    (SELECT @Payment)
    FOR XML PATH('Listing')

    ?? But "caching" the xml in variables affects performance. One can avoid this.

    2. Select the outer listing element by your own and write tree queries
    SELECT '<Listing xmlns="http://sql.listing" ID="'+@ID+'"'+'>' [XML-UID]
    ;WITH XMLNAMESPACES (DEFAULT 'http://sql.address') SELECT [query for address] FOR XML Path('Address')
    ... [same style queries for geo and payment]
    SELECT '</Listing>' [XML-UID]
    As you can see we made the outer element/namespace "by hand" and split the big query into several queries. The side-effect that we produce now several result sets will not harm the final result for a webservice / sqlxml template.


Share This Page