Selecting ordinary columns and columns formatted as XML in the same query-CodePudding

If I run select columnA from tableA I get the values from columnA from my table.

If I run select columnB, columnC from tableA as xml path, root I get the data from the 2 columns formatted as XML.

How do I go about combining the effects of the above 2 queries into one so that I get back columnA as-is and columnB with columnC as XML in a separate column labeled myXml?

I'm running this on a SQL2008R2 server.

CodePudding user response：

with myTable as (
    select a.columnA,
    (select b.columnA, b.columnB 
        from tableA b
        where b.columnA = a.columnA
        for xml path, root
    ) as myXml
    from tableA a
)
select * from myTable

I was getting so caught up in the XML, that I failed to see that I needed a self-join on the table with 2 aliases!