MS SQL 2005 Table to XML

Question

I have a simple table in SQL Server 2005, I wish to convert this to XML (using the "FOR XML" clause). I'm having trouble getting my XML to look like the required output.

I've tried looking through various tutorials on the web, but I am struggling. Can someone help?

The table I have looks like this

TYPE,GROUP,VALUE
Books,Hardback,56
Books,Softcover,34
CDs,Singles,45
CDS,Multis,78

The output style I need is:

<data>
  <variable name="TYPE">
   <row>
     <column>GROUP</column>
     <column>VALUE</column>
   </row>
   <row>
     <column>GROUP</column>
     <column>VALUE</column>
   </row>
  </variable>
 <variable name="TYPE">
   <row>
     <column>GROUP</column>
     <column>VALUE</column>
   </row>
   <row>
     <column>GROUP</column>
     <column>VALUE</column>
   </row>
  </variable>
</data>

Edit: As far as I can tell I require the multiple values. I'm generating XML for use with Xcelsius (Linking XML and Xcelsius) so have no control over in the formatting of the XML. I can generate the XML using ASP as per the linked tutorial, but I was hoping to get it straight from SQL Server.

Edit 2: I was hoping for something elegant and tidy... but Godeke's example got the closest. Some fiddling with the SQL and I've come up with:

select
   "type" as '@name', 
   "group" as 'row/column',
   null as 'row/tmp', 
   "value" as 'row/column'
from tableName
for xml path('variable'), root('data')

Outputs almost in the exact way I wanted. The null/tmp line doesn't even output; it is just preventing the concatenation. Still the tag <variable name="TYPE"> repeats for each row, which I can't have.

Answer 1

I prefer using for XML PATH, it provides a nicer way to control your elements etc.

See

But this is quite tricky

 /*
create table #tablename
(
[type] varchar(20),
[group] varchar(20),
[value] varchar(20)
)

insert into #tablename select 'type1','group11','value111'
insert into #tablename select 'type1','group11','value112'
insert into #tablename select 'type1','group12','value121'
insert into #tablename select 'type1','group12','value122'
insert into #tablename select 'type2','group21','value211'
insert into #tablename select 'type2','group21','value212'
insert into #tablename select 'type2','group22','value221'
insert into #tablename select 'type2','group22','value222'

alter table #tablename add id uniqueidentifier

update #tablename set id = newid()
*/

select [type] as '@name',
    (select     
        (select [column] from
            (
                select [group] as 'column', tbn1.type, tbn2.[group]
               from #tablename tbn3 WHERE tbn3.type = tbn1.type and tbn2.[group] =  tbn3.[group]
               union
         select [value], tbn1.type, tbn2.[group]
              from #tablename tbn3 WHERE tbn3.type = tbn1.type and tbn2.[group] = tbn3.[group]
            ) as s
        for xml path(''),type 
        )
    from #tablename tbn2 
    where tbn2.type = tbn1.type
    for xml path('row3'), type
)

from #tableName tbn1 
GROUP BY [type]
for xml path('variable'), root('data')

gives you what you are asking for I, but elegant and tidy it is not.

Answer 2

As close as I can get is this:

select "type" as '@name', "group" as 'row/column1', "value" as 'row/column2'
from tableName
for xml path('variable'), root('data')

Naming two items the same ("column" and "column") isn't something I know how to do in one pass, but on the other hand it is an odd XML schema choice; normally elements have unique names if they contain distinct data. The obvious choice (name them both 'row/column') simply concatenates them in the output into one value.

Also note that each returned row will be a "variable" element distinct from the others. To get the nesting without redundant records will require a subquery:

select distinct "type" as '@name'
from Agent
for xml path('variable'), root('data')

was my first thought, but the distinct prevents nesting.

All this makes me think that to get the exact output you need you might have to use EXPLICIT mode. Perhaps my problem is for something like this I punt and use a DOMDocument in code :).

Answer 3

The script below produces the desired format

<DATA>
    <VARIABLE TYPE="Books">
      <row TYPE="Books">
        <GROUP>Hardback</GROUP>
        <VALUE>56</VALUE>
      </row>
      <row TYPE="Books">
        <GROUP>Softcover</GROUP>
        <VALUE>34</VALUE>
      </row>
    </VARIABLE>
    <VARIABLE TYPE="CDs">
      <row TYPE="CDs">
        <GROUP>Singles</GROUP>
        <VALUE>45</VALUE>
      </row>
      <row TYPE="CDS">
        <GROUP>Multis</GROUP>
        <VALUE>78</VALUE>
      </row>
    </VARIABLE>
</DATA>

Invoke


DECLARE @tblItems table (
    [TYPE] varchar(50)
    ,[GROUP] varchar(50)
    ,[VALUE] int
)

DECLARE @tblShredded table (
    [TYPE] varchar(50)
    ,[XmlItem] xml
)

DECLARE @xmlGroupValueTuples xml

insert into @tblItems([TYPE],[GROUP],[VALUE]) values( 'Books','Hardback',56)
insert into @tblItems([TYPE],[GROUP],[VALUE]) values( 'Books','Softcover',34)
insert into @tblItems([TYPE],[GROUP],[VALUE]) values( 'CDs','Singles',45)
insert into @tblItems([TYPE],[GROUP],[VALUE]) values( 'CDS','Multis',78)

SET @xmlGroupValueTuples =
  (
    SELECT
      "@TYPE" = [TYPE]
      ,[GROUP]
      ,[VALUE]
    FROM @tblItems
    FOR XML PATH('row'), root('Root')
  )

INSERT @tblShredded([TYPE], XmlItem)
SELECT
    [TYPE] = XmlItem.value('./row[1]/@TYPE', 'varchar(50)')
    ,XmlItem
FROM dbo.tvfShredGetOneColumnedTableOfXmlItems(@xmlGroupValueTuples)


SELECT
  (
    SELECT
      VARIABLE =
        (
          SELECT
            "@TYPE" = t.[TYPE]

            ,(
              SELECT
                tInner.XmlItem.query('./child::*')
              FROM @tblShredded tInner
              WHERE tInner.[TYPE] = t.[TYPE]
              FOR XML PATH(''), ELEMENTS, type
            )
          FOR XML PATH('VARIABLE'),type
        )
  )
FROM @tblShredded t
GROUP BY
    t.[TYPE]
FOR XML PATH(''), ROOT('DATA')

where


-- Example Inputs
/*
DECLARE @xmlListFormat xml
SET @xmlListFormat =
    '
        <XmlListRoot>
            <Item>004421UB7</Item>
            <Item>59020UH24</Item>
            <Item>542514NA8</Item>
        </XmlListRoot>
    '
*/

-- =============================================
-- Author: 6eorge Jetson
-- Create date: 01/22/3003
-- Description: Shreds an input XML list conforming to the expected list schema
-- =============================================
CREATE FUNCTION [dbo].[tvfShredGetOneColumnedTableOfXmlItems] (@xmlListFormat xml)
RETURNS
@tblResults TABLE (XmlItem xml)
AS
BEGIN

    INSERT @tblResults
    SELECT
        tblShredded.colXmlItem.query('.') as XmlItem
    FROM
        @xmlListFormat.nodes('/child::*/child::*') as tblShredded(colXmlItem)

    RETURN
END