2uzhan.com
Advertisement
Now Place:2uzhan.com » Help creating XML output

Help creating XML output

Microsoft SQL Server @ October 18, 2011   Views:0

I've found dozens of examples of the FOR XML syntax, and I am lost understanding the fundamentals--time for a training seminar.

In the mean time, can one of you point me in the correct direction here.

Here's a test table of data I would like to output in XML:

Code:

declare    @TestTable
table   (
        Configuration varchar(50) not null
        ,Project varchar(10) null
        ,Phase varchar(10) null
        ,PRIMARY KEY
            (
            Configuration
            )

        )

insert into @TestTable values('Initialization','12345','6')
insert into @TestTable values('ProjectSelected','67890',null)
insert into @TestTable values('PhaseSelected','34567','8')

and here is what the data in the table looks like when returned with a simple SELECT statement:

Code:

Configuration                                      Project    Phase
-------------------------------------------------- ---------- ----------
Initialization                                     12345      6
ProjectSelected                                    67890      NULL
PhaseSelected                                      34567      8

and here is what I would like to get as the XML result of a SELECT against this table:

Code:

<Configuration Project="12345" Phase="6">Initialization</Configuration>
<Configuration Project="67890">Initialization</Configuration>
<Configuration Project="34567" Phase="8">Initialization</Configuration>

If the second line comes back with the Phase called-out with the NULL value, that's fine with me.

Can you guys point me in the right direction?

Thanks.

Oh. Using SQL2008, but not R2.

--------------Solutions-------------

Try the following for XML output of @TestTable:

Code:

select
  Project as "Configuration/@Project"
 ,Phase as "Configuration/@Phase"
 ,Configuration
from @TestTable
for xml path('') ,elements ,type

Homerow, thank you.

That is exactly what I needed. Now I understand how it works.

Ken

Tags:
© 2018 2uzhan.com Contact