在SQL Server中，我可以从表中将多个节点插入XML吗? [英] In SQL Server can I insert multiple nodes into XML from a table?

问题描述

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

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

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

The following insert allows me to add many nodes but they have to be hard-coded or use variables (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.

但是有一种方法可以通过与select语句组合并避免循环来做到这一点?

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

编辑我以前曾经使用SELECT FOR XML做类似的事情，但是在处理来自多个表的数据层次结构时，我总是很难看懂.我希望使用modify可以使生成的XML更明确和更可控.

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.

推荐答案

您是否尝试过嵌套 FOR XML PATH标量值函数? 借助嵌套技术，您可以将SQL分解为易于管理/可读的基本元素

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

免责声明:以下内容是根据实际示例改编而成，并未经过字面测试

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

• 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

考虑以下调用

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

假设udfGetLowestLevelNestedNode_SpecificDogName的编写没有FOR XML PATH子句，对于@NestedInput_SpecificDogName = 99，它返回单个行集记录:

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:

@SpecificDogNameId  DogName
99                  Astro

但是使用FOR XML PATH子句

But with the FOR XML PATH clause,

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

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

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

<Dog SpecificDogNameId=99>Astro</Dog>

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

Nesting User-defined Functions of XML Type

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

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

例如，函数

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

以

SELECT [dbo].[udfGetDogCollectionNode]()

可能会生成复杂的XML节点(如果有适当的基础数据)

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

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

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

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

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

以

SELECT [dbo].[udfGetAnimalCollectionNode]()

udf可能会产生更复杂的XML节点(如果有适当的基础数据)

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

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

这篇关于在SQL Server中，我可以从表中将多个节点插入XML吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！