SQL Server Performance Forum – Threads Archive

a way to generate dynamic ‘for xml path’?

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

---

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 – 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.

---

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

---

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"

---

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

---

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

---

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

---

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.

---

]]>