MS SQL 2005 表到 XML

发布于 2024-07-08 16:16:57 字数 1527 浏览 8 评论 0原文

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

我尝试在网上浏览各种教程，但我很挣扎。有人可以帮忙吗？

我的表格看起来像这样

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

我需要的输出样式是：

<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>

编辑： 据我所知，我需要多个值。我正在生成用于 Xcelsius 的 XML（链接 XML 和 Xcelsius ）因此无法控制 XML 的格式。我可以按照链接教程使用 ASP 生成 XML，但我希望直接从 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 行甚至不输出；它只是阻止串联。标签仍然对每一行重复，这是我无法拥有的。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

伪心 2024-07-15 16:16:57

我所能得到的最接近的是：

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

将两个项目命名为相同的（“column”和“column”）并不是我知道如何一次完成的事情，但另一方面，这是一个奇怪的 XML 模式选择；通常，如果元素包含不同的数据，则它们具有唯一的名称。显而易见的选择（将它们都命名为“行/列”）只是将输出中的它们连接成一个值。

另请注意，每个返回的行都将是一个与其他行不同的“变量”元素。要获得没有冗余记录的嵌套，将需要子查询：

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

这是我的第一个想法，但不同的会阻止嵌套。

所有这些让我认为，要获得您需要的准确输出，您可能必须使用 EXPLICIT 模式。也许我的问题是这样的，我在代码中使用 DOMDocument :)。

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

回复收藏 0 原文

标点 2024-07-15 16:16:57

我更喜欢使用 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')

无法满足您的要求，但它并不优雅和整洁。

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.

回复收藏 0 原文

等待我真够勒 2024-07-15 16:16:57

下面的脚本生成所需的格式

<数据>

<变量类型=“书籍”>

<行类型=“书籍”>

<组>精装本

<值>56

<行类型=“书籍”>

平装本

<值>34

<变量类型=“CD”>

<行 TYPE="CD">

<团体>单打

<值>45

<行类型=“CDS”>

多重

<值>78

调用

<代码>

声明 @tblItems 表 (

[类型] varchar(50)

,[组] varchar(50)

,[值] 整数

）
声明 @tblShredded 表 (

[类型] varchar(50)

,[XmlItem] xml

）
声明 @xmlGroupValueTuples xml
插入@tblItems([TYPE],[GROUP],[VALUE])values('Books','Hardback',56)

插入@tblItems([TYPE],[GROUP],[VALUE])values('Books','Softcover',34)

插入@tblItems([TYPE],[GROUP],[VALUE])values('CDs','Singles',45)

插入@tblItems([TYPE],[GROUP],[VALUE])values('CDS','Multis',78)
设置@xmlGroupValueTuples =

(

选择

“@TYPE”=[类型]

，[团体]

，[价值]

来自@tblItems

FOR XML PATH('行'), 根('根')

）
插入@tblShredded（[类型]，XmlItem）

选择

[TYPE] = XmlItem.value('./row[1]/@TYPE', 'varchar(50)')

,XmlItem

来自 dbo.tvfShredGetOneColumnedTableOfXmlItems(@xmlGroupValueTuples)
选择

(

选择

变量=

(

选择

“@TYPE”= t.[类型]
,(

选择

tInner.XmlItem.query('./child::*')

来自@tblShredded tInner

其中 tInner.[类型] = t.[类型]

FOR XML 路径（''），元素，类型

）

FOR XML 路径（'变量'），类型

）

）

来自@tblShredded t

分组依据

t.[类型]

对于 XML 路径（''），根（'数据'）

哪里

<代码>

-- 输入示例

/*

声明@xmlListFormat xml

设置@xmlListFormat =

'

<项目>004421UB7

<项目>59020UH24

<项目>542514NA8

'

*/
-- ===============================================

-- 作者：6eorge Jetson

-- 创建日期：01/22/3003

-- 描述：粉碎符合预期列表模式的输入 XML 列表

-- ===============================================

创建函数 [dbo].[tvfShredGetOneColumnedTableOfXmlItems] (@xmlListFormat xml)

退货

@tblResults 表（XmlItem xml）

AS

开始
插入@tblResults

选择

tblShredded.colXmlItem.query('.') 作为 XmlItem

从

@xmlListFormat.nodes('/child::*/child::*') as tblShredded(colXmlItem)
返回

完

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