Select SQL to XML without concatenating columns

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-21T13:29:42.230

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>

Sorry I should have highlighted that I understand how to get that format, but how would you get it to be someurlsomeurl#2? — Nick Spicer, Sep 21 '15 at 13:25
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. http://stackoverflow.com/questions/29088506/sql-server-for-xml-path-make-repeating-nodes — JBond, Sep 21 '15 at 13:30
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! — Nick Spicer, Sep 21 '15 at 13:53

Select SQL to XML without concatenating columns

1 Answers1