MS SQL 2005 Table for XML

Question

I have a simple table in SQL Server 2005, I want to convert it to XML (using the FOR XML clause). I am having trouble getting my XML to look like the required output.

I tried to browse various tutorials on the Internet, but I'm afraid. Can anyone help?

In a table that looks like this

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

I need an output style:

<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 need a few values. I am creating XML for use with Xcelsius ( Linking XML and Xcelsius ), so I have no control over XML formatting. I can generate XML using ASP according to the related tutorial, but I was hoping to get it directly from SQL Server.

2: - ... . SQL, :

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

, . null/tmp ; . <variable name="TYPE"> , .

Answer 1

, :

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

( "" "" ) , , , , , XML; , . ( "/" ) .

, "variable", . :

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

, .

, EXPLICIT. , , , DOMDocument :).

Answer 2

XML PATH, ..

.

 /*
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') 

, , , .

Answer 3

script

< >
  < =" " >
  < row TYPE =" Books" >
    < & GT; & ; /& GT;
    < & VALUE GT; 56 </& VALUE GT;
  </ >
  < row TYPE =" Books" >
    < & GT; Softcover </& GT;
    < & VALUE GT; 34 </& VALUE GT;
  </ >
  </ >
  < =" CD" >
  < row TYPE =" CD" >
    < & GT; & ; /& GT;
    < & VALUE GT; 45 </& VALUE GT;
  </ >
  < row TYPE =" CDS" >
    < & GT; Multis </& GT;
    < & VALUE GT; 78 </& VALUE GT;
  </ >
  </ VARIABLE>
</ DATA>

To call


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