SQL Server Performance

a way to generate dynamic 'for xml path'?

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by SQL_Guess, Aug 4, 2006.

  1. SQL_Guess New Member

    I currently have some T-SQL generated as a column:



    SELECT
    BL.[CatalogueItemBuyListSequenceNumber]as "@__Value"
    FROM
    [integration].[BuyListProductRecipes] BL
    WHERE
    BL.[CatalogueItemID] = CL.[CatalogueItemID]
    AND CURRENT_TIMESTAMP BETWEEN BL.BuyListStartDate AND BL.BuyListEndDate
    for xml path('BuyListSequence_')



    I am trying to find out if there is a way of having the path name dynamic, without having to write dynamic SQL. i.e. something like:


    SELECT
    BL.[CatalogueItemBuyListSequenceNumber]as "@__Value"
    FROM
    [integration].[BuyListProductRecipes] BL
    WHERE
    BL.[CatalogueItemID] = CL.[CatalogueItemID]
    AND CURRENT_TIMESTAMP BETWEEN BL.BuyListStartDate AND BL.BuyListEndDate
    for xml path('BuyListSequence_'+CONVERT(VARCHAR,BL.[CatalogueItemBuyListSequenceNumber]))


    Naturally, that doesn't work.

    Anyone done anything like this?


    Panic, Chaos, Disorder ... my work here is done --unknown
  2. Adriaan New Member

    Check here -http://msdn.microsoft.com/sql/learn...ull=/library/en-us/dnsql90/html/forxml2k5.asp ...

    I love this quote on the old situation, with SQL 2000 -

    quote:The EXPLICIT mode format is highly successful in achieving its goals. There are only a few things it cannot do (arbitrary recursive part-list trees are one of them). The preferred SQL expression to generate the rowset format is a formidable "query from hell," however.
    Any time someone asked me to dump data to XML files, recursion was a major part of it ... As we're still in SQL 2000, we're faking the whole lot.

    Anyway ... the referenced article also has a section on SQL 2005's new PATH mode which seems relevant.
  3. SQL_Guess New Member

    Thanks Adriaan,

    I'll look into the recursion. I'm still not sure if it will answer my problem, since the PATH name [for xml path ('PathName')] appears to requirea literal value - it will not accept a variable, so even a recursive solution would still generate the same PathName for my result set:

    This is what was requested as output (xml get's ruined by the forums, so I'll use TABS to indicate levels:

    ProductInformationEnhancements CatalogueItemID="100-0030"
    BuyList __Ref="BuyListID" BuyListID="15" BuyListName="Strata" BuyListSequence="2"
    BuyList __Ref="BuyListID" BuyListID="20" BuyListName="Strata" BuyListSequence="1"
    PriceCheck CompetitorName="Argos" CompetitorPrice="8.99" CheckDate="2006-06-17"
    PriceCheck CompetitorName="Comet" CompetitorPrice="8.99" CheckDate="2006-06-17"
    BuyListSequence_15 __Value="2"
    BuyListSequence_20 __Value="1"
    UnitsSold __Value="34"
    ProductInformationEnhancements


    Note the bold values showing the dynamic requirement/linking.

    Since it is the [for xml path ('PathName')] statement that creates the name, that is what needs to be dynamic

    Panic, Chaos, Disorder ... my work here is done --unknown
  4. Adriaan New Member

    Not sure how your data model is set up, but it is confusing to say the least.

    I can understand these two attributes:
    BuyListID="15"
    BuyListSequence="2"

    ... but not this derived whatever-it-is:
    BuyListSequence_15 __Value="2"
  5. SQL_Guess New Member

    It's being driven by requirements from a third party application. They require columns named in a specific format to be able to process them for different operations - in this case they claim they require this to be abled to sort them.

    We've managed to find an acceptable work around that does not require (a) dynamic SQL, (b) significant code change in my SQL or the calling layer of BizTalk code. As you may have guessed, this was trying to be 'kludged' in SQL because I had less work on my plate than others, so they figured to push it into SQL.

    I still think there should have been no work done in SQL - this should have been done, if necessary, in the Biztalk solution, but in the end we've managed to kick back to a more pragmatic solution (although, imho, it's still not great) but given that development for this project finished 2 weeks ago, and we are 1/2 through testing, I think this is a battle I will have to choose to avoid.

    CiaO

    Panic, Chaos, Disorder ... my work here is done --unknown
  6. Adriaan New Member

    Looks like you'll have to it the old-fashioned way, iterating through a cursor to compile dynamic statements for each XML dump.
  7. SQL_Guess New Member

    I managed to escape that. Between the Biztalk developr and myself, we agreed to give him a specifically named attribute in the element that he can read and use to generate the final XML message for the 3rd party application (he's already doing XML transformations in this piece, so it was a reletively minor change for him). The final SQL is:



    SELECT
    BL.[CatalogueItemBuyListSequenceNumber]as "@__Value",
    'BuyListSequence_'+CONVERT(VARCHAR,BL.[BuyListID])as "@__Name"
    FROM
    [integration].[BuyListProductRecipes] BL
    WHERE
    BL.[CatalogueItemID] = CL.[CatalogueItemID]
    AND CURRENT_TIMESTAMP BETWEEN BL.BuyListStartDate AND BL.BuyListEndDate
    for xml path('BuyListSequence'


    (This is just a fragment of the full SQL, hence the CL. reference).

    Biztalk will interpret the __Name attribute, and use that value in naming the Element for the 3rd party.

    Thanks for the responses and help!

    Panic, Chaos, Disorder ... my work here is done --unknown
  8. Adriaan New Member

    Makes sense (in as much as XML ever makes sense).

    Ah, 3rd party requirements ... But you're absolutely right: this is a presentation issue that should not be reflected in the XML.

Share This Page