在SQL Server中，我可以从表中将多个节点插入XML吗？

I want to generate some XML in a stored procedure based on data in a table.

我想基于表中的数据在存储过程中生成一些XML。

The following insert allows me to add many nodes but they have to be hard-coded or use variables (sql:variable):

以下插入允许我添加许多节点,但它们必须是硬编码或使用变量(sql:variable):

SET @MyXml.modify('
      insert
         <myNode>
            {sql:variable("@MyVariable")}
         </myNode>
      into (/root[1]) ') 

So I could loop through each record in my table, put the values I need into variables and execute the above statement.

所以我可以循环遍历表中的每条记录,将我需要的值放入变量并执行上面的语句。

But is there a way I can do this by just combining with a select statement and avoiding the loop?

但是,有没有办法通过组合select语句并避免循环来实现这一点?

Edit I have used SELECT FOR XML to do similar stuff before but I always find it hard to read when working with a hierarchy of data from multiple tables. I was hoping there would be something using the modify where the XML generated is more explicit and more controllable.

编辑我之前使用过SELECT FOR XML来做类似的事情但是我总是发现在处理来自多个表的数据层次结构时很难阅读。我希望有一些东西可以使用修改,其中生成的XML更明确,更可控。

3 个解决方案

#1

Have you tried nesting FOR XML PATH scalar valued functions? With the nesting technique, you can brake your SQL into very managable/readable elemental pieces

您是否尝试嵌套FOR XML PATH标量值函数?使用嵌套技术,您可以将SQL制作成非常易于管理/可读的元素

Disclaimer: the following, while adapted from a working example, has not itself been literally tested

免责声明:以下内容,虽然改编自一个工作示例,但本身并未进行过字面测试

Some reference links for the general audience

一般观众的一些参考链接

• http://msdn2.microsoft.com/en-us/library/ms178107(SQL.90).aspx
• http://msdn2.microsoft.com/en-us/library/ms189885(SQL.90).aspx

The simplest, lowest level nested node example

最简单,最低级别的嵌套节点示例

Consider the following invocation

考虑以下调用

DECLARE  @NestedInput_SpecificDogNameId int
SET @NestedInput_SpecificDogNameId = 99
SELECT [dbo].[udfGetLowestLevelNestedNode_SpecificDogName] 
(@NestedInput_SpecificDogNameId)

Let's say had udfGetLowestLevelNestedNode_SpecificDogName had been written without the FOR XML PATH clause, and for @NestedInput_SpecificDogName = 99 it returns the single rowset record:

假设在没有FOR XML PATH子句的情况下编写了udfGetLowestLevelNestedNode_SpecificDogName,并且对于@NestedInput_SpecificDogName = 99,它返回单行集记录:

@SpecificDogNameId  DogName
99                  Astro

But with the FOR XML PATH clause,

但是使用FOR XML PATH子句,

CREATE FUNCTION dbo.udfGetLowestLevelNestedNode_SpecificDogName
(
@NestedInput_SpecificDogNameId
)
    RETURNS XML
    AS
    BEGIN

        -- Declare the return variable here
        DECLARE @ResultVar XML

        -- Add the T-SQL statements to compute the return value here
        SET @ResultVar =
            (
            SELECT 
                  @SpecificDogNameId as "@SpecificDogNameId",
                  t.DogName 
            FROM tblDogs t
            FOR XML PATH('Dog')
            )

        -- Return the result of the function
        RETURN @ResultVar

END

the user-defined function produces the following XML (the @ signs causes the SpecificDogNameId field to be returned as an attribute)

用户定义的函数生成以下XML(@符号使得SpecificDogNameId字段作为属性返回)

<Dog SpecificDogNameId=99>Astro</Dog>

Nesting User-defined Functions of XML Type

嵌套XML类型的用户定义函数

User-defined functions such as the above udfGetLowestLevelNestedNode_SpecificDogName can be nested to provide a powerful method to produce complex XML.

用户定义的函数(如上面的udfGetLowestLevelNestedNode_SpecificDogName)可以嵌套,以提供生成复杂XML的强大方法。

For example, the function

例如,功能

CREATE FUNCTION [dbo].[udfGetDogCollectionNode]()
    RETURNS XML
    AS
    BEGIN

        -- Declare the return variable here
        DECLARE @ResultVar XML

        -- Add the T-SQL statements to compute the return value here
        SET @ResultVar =
            (
                SELECT  
                [dbo].[udfGetLowestLevelNestedNode_SpecificDogName]
                        (t.SpecificDogNameId)
                FROM tblDogs t

                FOR XML PATH('DogCollection') ELEMENTS
            )
        -- Return the result of the function
        RETURN @ResultVar

END

when invoked as

当被调用为

SELECT [dbo].[udfGetDogCollectionNode]()

might produce the complex XML node (given the appropriate underlying data)

可能会产生复杂的XML节点(给定适当的底层数据)

<DogCollection>
    <Dog SpecificDogNameId="88">Dino</Dog>
    <Dog SpecificDogNameId="99">Astro</Dog>
</DogCollection>

From here, you could keep working upwards in the nested tree to build as complex an XML structure as you please

从这里开始,您可以继续在嵌套树中向上工作,以便根据需要构建复杂的XML结构

CREATE FUNCTION [dbo].[udfGetAnimalCollectionNode]()
RETURNS XML
AS
BEGIN

DECLARE @ResultVar XML

SET @ResultVar =
(
SELECT 
dbo.udfGetDogCollectionNode(),
dbo.udfGetCatCollectionNode()
FOR XML PATH('AnimalCollection'), ELEMENTS XSINIL
)

RETURN @ResultVar

END

when invoked as

当被调用为

SELECT [dbo].[udfGetAnimalCollectionNode]()

the udf might produce the more complex XML node (given the appropriate underlying data)

udf可能会产生更复杂的XML节点(给定适当的底层数据)

<AnimalCollection>
  <DogCollection>
    <Dog SpecificDogNameId="88">Dino</Dog>
    <Dog SpecificDogNameId="99">Astro</Dog>
  </DogCollection>
  <CatCollection>
    <Cat SpecificCatNameId="11">Sylvester</Cat>
    <Cat SpecificCatNameId="22">Tom</Cat>
    <Cat SpecificCatNameId="33">Felix</Cat>
  </CatCollection>
</AnimalCollection>

#2

Can you tell a bit more about what exactly you are planning to do. Is it simply generating XML data based on a content of the table or adding some data from the table to an existing xml structure?

你能详细说明你打算做什么吗?它只是根据表的内容生成XML数据,还是将表中的一些数据添加到现有的xml结构中?

There are great series of articles on the subject on XML in SQLServer written by Jacob Sebastian, it starts with the basics of generating XML from the data in the table

在Jacob Sebastian编写的SQLServer中有关于XML主题的大量系列文章,它首先从表格中的数据生成XML的基础知识开始

#3

Use sql:column instead of sql:variable. You can find detailed info here: http://msdn.microsoft.com/en-us/library/ms191214.aspx

使用sql:column而不是sql:variable。您可以在此处找到详细信息:http://msdn.microsoft.com/en-us/library/ms191214.aspx