Select SQL to XML without concatenating columns [duplicate]

Question

I have a table which needs to be converted into XML however using the same XML element name concatinates the string of every column that tries to use it.

SELECT  
    SKU [SKU]   
    , title [Title]     
    , URL [URL]     
    , promotionsURL1 [PROMOTIONS/URL]   
    , promotionsURL2 [PROMOTIONS/URL]   
    , promotionsURL3 [PROMOTIONS/URL]   
    , promotionsURL4 [PROMOTIONS/URL]   
    , promotionsURL5 [PROMOTIONS/URL]   
    , promotionsURL6 [PROMOTIONS/URL] 
FROM tblSLIRecommendations 
FOR XML PATH('SKU_TO_TUNE'), ROOT('RECOMMENDATION_TUNINGS')

This gives me the XML result

<SKU_TO_TUNE>
    <SKU>VS4111-546U135-U135</SKU>
    <Title>Twirly Swiggles</Title>
    <URL>http://www.sweetcandy.com/c-p/VS4111-546U135-U135</URL>
    <PROMOTIONS>
        <URL>http://www.sweetcandy.com/c-p/SA4211-472U135-U135http://www.sweetcandy.com/c-p/RK0179-000P143-P143http://www.sweetcandy.com/c-p/WJ4038-477U135-U135http://www.sweetcandy.com/c-p/HH3577-209U136-U136</URL>
    </PROMOTIONS>
</SKU_TO_TUNE>

Whereas I would prefer the output like this but without needing the numbers after URL

 <SKU_TO_TUNE>
    <SKU>VS4111-546U135-U135</SKU>
    <Title>Twirly Swiggles</Title>
    <URL>http://www.sweetcandy.com/c-p/VS4111-546U135-U135</URL>
    <PROMOTIONS>
      <URL1>http://www.sweetcandy.com/c-p/SA4211-472U135-U135</URL1>
      <URL2>http://www.sweetcandy.com/c-p/RK0179-000P143-P143</URL2>
      <URL3>http://www.sweetcandy.com/c-p/WJ4038-477U135-U135</URL3>
      <URL4>http://www.sweetcandy.com/c-p/HH3577-209U136-U136</URL4>
    </PROMOTIONS>
</SKU_TO_TUNE>

Is there a simple way to get round this?

JBond · Accepted Answer · 2015-09-21 13:29:42Z

1

You want to nest the columns you want to output as nested XML:

Here is a working example:

IF OBJECT_ID('tempdb..#Promotions','U') IS NOT NULL
    DROP TABLE #Promotions

GO


CREATE TABLE #Promotions (
    SKU VARCHAR(MAX),
    Title VARCHAR(MAX),
    PromotionURl1 VARCHAR(MAX),
    PromotionURl2 VARCHAR(MAX),
    PromotionURl3 VARCHAR(MAX),
    PromotionURl4 VARCHAR(MAX),
)

INSERT #Promotions (SKU, Title, PromotionURl1, PromotionURl2, PromotionURl3, PromotionURl4)
    VALUES ('VS4111-546U135-U135', 'Twirly Swiggles', 'http://www.sweetcandy.com/c-p/SA4211-472U135-U135', 'http://www.sweetcandy.com/c-p/RK0179-000P143-P143', 'http://www.sweetcandy.com/c-p/WJ4038-477U135-U135', 'http://www.sweetcandy.com/c-p/HH3577-209U136-U136');


SELECT
    SKU,
    Title,
    (SELECT
        PromotionURl1 URL, NULL,
        PromotionURl2 URL, NULL,
        PromotionURl3 URL, NULL,
        PromotionURl4 URL
    FOR XML PATH ('Promotions'), TYPE)
FROM #Promotions
FOR XML PATH ('SKU_TO_TUNE'), ROOT ('RECOMMENDATION_TUNINGS')

Giving you the following output:

<RECOMMENDATION_TUNINGS>
  <SKU_TO_TUNE>
    <SKU>VS4111-546U135-U135</SKU>
    <Title>Twirly Swiggles</Title>
    <Promotions>
      <URL>http://www.sweetcandy.com/c-p/SA4211-472U135-U135</URL>
      <URL>http://www.sweetcandy.com/c-p/RK0179-000P143-P143</URL>
      <URL>http://www.sweetcandy.com/c-p/WJ4038-477U135-U135</URL>
      <URL>http://www.sweetcandy.com/c-p/HH3577-209U136-U136</URL>
    </Promotions>
  </SKU_TO_TUNE>
</RECOMMENDATION_TUNINGS>

edited Sep 21, 2015 at 13:29

answered Sep 21, 2015 at 12:35

JBond

3,3025 gold badges31 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nick Spicer Over a year ago

Sorry I should have highlighted that I understand how to get that format, but how would you get it to be <promotions><URL>someurl</URL><URL>someurl#2</URL><promotions/>?

JBond Over a year ago

No problem. I misunderstood the question. I've edited my answer now to do this. You'll see the same method in the link posted by the moderators at the top of this question. stackoverflow.com/questions/29088506/…

Nick Spicer Over a year ago

Amazing thank you, I saw that answer and attempted it as well but couldn't get it working because it wasn't a direct child of the parent - I understand how it's working now with your example!

Collectives™ on Stack Overflow

Select SQL to XML without concatenating columns [duplicate]

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related