For XML question

Question

For XML question

TJ_T

Old Hand

Points: 352
More actions
March 7, 2019 at 6:55 am

#104497

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 one
SELECT '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>

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

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

TJ_T Old Hand Points: 352 More actions · Answer 2

TJ_T

Old Hand

Points: 352

March 7, 2019 at 8:18 am

#2023837

Wow! Very nice. 🙂