CODEWITHSUNDEEP
sql-serverxmlt-sqlalias
Bee_Riii
Bee_Riii

Reputation: 1039

How can I create a select query that returns columns and rows and then combines some of the columns into XML

In TSQL is there a function to select a row as normal and then as an aliased column return some of those fields in XML format.

I'm creating an error log and I'd like to insert into a table the Primary key as a standard column and then some of the fields in the table as combined xml. I'm pretty new to dealing with xml and I've found out how to return xml data but not as a aliased column of a select query.

Is this possible? I apologise if this has been answered already. I have a feeling I just don't know what the right search terms are.

Thanks!

Upvotes: 0

Views: 39

Answers (1)

Rhys Jones
Rhys Jones

Reputation: 5518

You can use a sub-select with FOR XML to to this, something along the lines of this;

select
    id,
    column1,
    column2,
    column3,
    (select
        column4,
        column5,
        column6
     from 
        my_table t2
     where
        t2.id = t1.id
     for xml path, type) as columnx
from
    my_table t1

Upvotes: 1

Related Questions