Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601

    Unanswered: Help creating XML output

    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.
    Last edited by PracticalProgram; 10-19-11 at 12:27.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  2. #2
    Join Date
    Sep 2011
    Location
    Greenville, SC USA
    Posts
    34

    RE: Help creating XML output

    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

  3. #3
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Homerow, thank you.

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

    Ken
    Ken

    Maverick Software Design

    (847) 864-3600 x2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •