MS SQL 2005表到XML

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.

我在SQL Server 2005中有一个简单的表,我希望将其转换为XML(使用“FOR XML”子句)。我无法让我的XML看起来像所需的输出。

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.

编辑:据我所知,我需要多个值。我正在生成用于Xcelsius的XML(链接XML和Xcelsius),因此无法控制XML的格式。我可以根据链接教程使用ASP生成XML,但我希望从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:

编辑2:我希望有一些优雅和整洁的东西......但是Godeke的榜样最接近。一些摆弄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')

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.

输出几乎与我想要的完全相同。 null / tmp行甚至不输出;它只是阻止连接。仍然标签 为每一行重复,这是我不能拥有的。

3 个解决方案

#1

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.

命名两个相同的项(“列”和“列”)并不是我知道如何在一次传递中做的事情,但另一方面它是一个奇怪的XML模式选择;如果元素包含不同的数据,则它们具有唯一的名称显而易见的选择(将它们命名为'row / column')只是将它们在输出中连接成一个值。

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 :).

所有这些让我觉得要获得所需的确切输出,您可能必须使用EXPLICIT模式。也许我的问题是这样的事情,我在平台上使用DOMDocument :)。

#2

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

我更喜欢使用XML PATH,它提供了一种更好的方法来控制你的元素等。

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.

给你我要求的东西,但优雅和整洁不是。

#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