Formatting sql correctly tp produce expected xml output

Question

Thanks to a fellow SO user I'm making progress with FOR XML, but I'm clearly not quite getting the sql syntax correct.

Let's say that I have the following sql

DECLARE @Uname VARCHAR(15) = 'Dom',
        @Pword VARCHAR(15) = 'Monty'

SELECT 
   RTRIM(@Uname) AS '@uname',
   RTRIM( @Pword) AS '@pword',
   (SELECT COALESCE(PortOfLanding,'') AS portOfLanding 
    FROM Landings.LandingHeaders  
    WHERE Posted = 0 
    FOR XML PATH('Sale')) 
FOR XML PATH('abc')

When run it produces the following as its output

<abc uname="Dom" pword="Monty">&lt;Sale&gt;&lt;portOfLanding&gt;GBHTG&lt;/portOfLanding&gt;&lt;/Sale&gt;&lt;Sale&gt;&lt;portOfLanding&gt;GBHTG&lt;/portOfLanding&gt;&lt;/Sale&gt;</abc>

What I was really hoping for though was the following

<abc uname="Dom" pword="Python">
    <Sale portOfLanding= "GBHTG" />
    <Sale portOfLanding= "GBHTG"/>
</abc>

and in fact I would like to add a third section to the SQL so that eventually one might end up with xml like so

<abc uname="Dom" pword="Python">
    <Sale portOfLanding= "GBHTG">
        <saleline detail="some value here" />
        <saleline detail="some value here" />
    <Sale/>
    <Salesnote portOfLanding= "GBHTG"/>
</abc>

Can someone point out where I've gone wrong in the original SQL query?

Thanks

Giorgi Nakeuri · Accepted Answer · 2015-05-15 16:23:52Z

1

Try this:

DECLARE @Uname VARCHAR(15) = 'Dom',
        @Pword VARCHAR(15) = 'Monty';

SELECT RTRIM(@Uname) AS '@uname',
       RTRIM( @Pword) AS '@pword', 
      (SELECT COALESCE(PortOfLanding,'') AS '@portOfLanding' 
       FROM Landings.LandingHeaders  
       WHERE Posted = 0 
       FOR XML PATH('Sale'), TYPE)
FOR XML PATH('abc')

You can go deeper like:

SELECT RTRIM(@Uname) AS '@uname',
       RTRIM( @Pword) AS '@pword', 
      (SELECT COALESCE(PortOfLanding,'') AS '@portOfLanding',
             (SELECT COALESCE(PortOfLanding,'') AS '@detail'
              FROM Landings.LandingHeaders  
              FOR XML PATH('SaleLine'), TYPE)
       FROM Landings.LandingHeaders  
       WHERE Posted = 0 
       FOR XML PATH('Sale'), TYPE)
FOR XML PATH('abc')

answered May 15, 2015 at 16:23

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

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

Collectives™ on Stack Overflow

Formatting sql correctly tp produce expected xml output

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related