For XML question
-
I am trying put some structure around my XML and running into an issue. I would like <First>, <Second>, <Third> under the root <Start>
Is it possible to create an empty namespace? It seems Path is fixed at oneSELECT 'ModelName' = (SELECT STRING_AGG(MNames, ', ')
FROM manufacturers),'Vendor' = (SELECT STRING_AGG(VNames, ', ')
FROM manufacturers)
FOR XML PATH ('First') , ROOT ('Start')I have this:
<Start>
<First>
<ModelName>Sterling</ModelName>
<Vendor>Amco, ABC Corp.</Vendor>
</First>
</Start>I would like this <First> and <Second>:
<Start>
<First>
<ModelName>Sterling</ModelName>
<Vendor>Amco, ABC Corp.</Vendor>
</First>
<Second>
</Second>
</Start> - This will sort of give you what you want, but you have to be able to order your results and also what happens with more than 3 items. If you want <Fourth>,<Fifth>... then its very complex.
with src as (
select MNames,VNames,row_number() over(order by VNames) as rn
from manufacturers
)
select
case when rn = 1 then MNames end as "First/ModelName",
case when rn = 1 then VNames end as "First/Vendor",
case when rn = 2 then MNames end as "Second/ModelName",
case when rn = 2 then VNames end as "Second/Vendor",
case when rn = 3 then MNames end as "Third/ModelName",
case when rn = 3 then VNames end as "Third/Vendor"
from src
order by VNames
for xml path(''), root('Start');____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Wow! Very nice.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply