在 SQL Server 中，我可以将多个节点从表中插入 XML 吗? [英] In SQL Server can I insert multiple nodes into XML from a table?
问题描述
我想根据表中的数据在存储过程中生成一些 XML.
以下插入允许我添加许多节点，但它们必须是硬编码或使用变量(sql:variable):
SET @MyXml.modify('插入<我的节点>{sql:variable("@MyVariable")}</myNode>进入 (/root[1]) ')所以我可以遍历表中的每条记录，将我需要的值放入变量中并执行上述语句.
但是有没有一种方法可以通过结合 select 语句并避免循环来做到这一点?
编辑 我以前使用过 SELECT FOR XML 来做类似的事情，但是在处理来自多个表的数据层次结构时，我总是觉得很难阅读.我希望使用 modify 生成的 XML 更明确、更可控.
您是否尝试过嵌套 FOR XML PATH 标量值函数?使用嵌套技术，您可以将 SQL 分解为非常易于管理/可读的元素片段
免责声明:以下内容虽然改编自一个工作示例，但本身并未经过实际测试
一些供普通观众参考的链接
- http://msdn2.microsoft.com/en-us/library/ms178107(SQL.90).aspx
- http://msdn2.microsoft.com/en-us/library/ms189885(SQL.90).aspx
最简单、最底层的嵌套节点示例
考虑以下调用
DECLARE @NestedInput_SpecificDogNameId intSET @NestedInput_SpecificDogNameId = 99选择 [dbo].[udfGetLowestLevelNestedNode_SpecificDogName](@NestedInput_SpecificDogNameId)假设 udfGetLowestLevelNestedNode_SpecificDogName 是在没有 FOR XML PATH 子句的情况下编写的，并且对于 @NestedInput_SpecificDogName = 99 它返回单个行集记录:
<上一页>@SpecificDogNameId 狗名99 天文但是使用 FOR XML PATH 子句，
CREATE FUNCTION dbo.udfGetLowestLevelNestedNode_SpecificDogName(@NestedInput_SpecificDogNameId)返回 XML作为开始-- 这里声明返回变量声明 @ResultVar XML-- 在此处添加计算返回值的 T-SQL 语句SET @ResultVar =(选择@SpecificDogNameId 为@SpecificDogNameId"，t.DogNameFROM tblDogs tFOR XML PATH('狗'))-- 返回函数的结果返回@ResultVar结尾用户定义函数生成以下 XML(@ 符号导致 SpecificDogNameId 字段作为属性返回)
<Dog SpecificDogNameId=99>Astro</Dog>嵌套 XML 类型的用户定义函数
上面的udfGetLowestLevelNestedNode_SpecificDogName等用户自定义函数可以嵌套，提供强大的方法来生成复杂的XML.
例如函数
创建函数 [dbo].[udfGetDogCollectionNode]()返回 XML作为开始-- 这里声明返回变量声明 @ResultVar XML-- 在此处添加计算返回值的 T-SQL 语句SET @ResultVar =(选择[dbo].[udfGetLowestLevelNestedNode_SpecificDogName](t.SpecificDogNameId)FROM tblDogs tFOR XML PATH('DogCollection') 元素)-- 返回函数的结果返回@ResultVar结尾当调用为
SELECT [dbo].[udfGetDogCollectionNode]()可能会产生复杂的 XML 节点(给定适当的基础数据)
<DogCollection><Dog SpecificDogNameId="88">Dino</Dog><Dog SpecificDogNameId="99">Astro</Dog></DogCollection>从这里开始，您可以继续在嵌套树中向上工作，以构建任意复杂的 XML 结构
创建函数 [dbo].[udfGetAnimalCollectionNode]()返回 XML作为开始声明 @ResultVar XMLSET @ResultVar =(选择dbo.udfGetDogCollectionNode(),dbo.udfGetCatCollectionNode()FOR XML PATH('AnimalCollection'), 元素 XSINIL)返回@ResultVar结尾当调用为
SELECT [dbo].[udfGetAnimalCollectionNode]()udf 可能会生成更复杂的 XML 节点(给定适当的基础数据)
<AnimalCollection><狗收藏><Dog SpecificDogNameId="88">Dino</Dog><Dog SpecificDogNameId="99">Astro</Dog></DogCollection><猫收藏><Cat SpecificCatNameId="11">西尔维斯特</Cat><Cat SpecificCatNameId="22">汤姆</Cat><Cat SpecificCatNameId="33">Felix</Cat></CatCollection></动物收藏>I want to generate some XML in a stored procedure based on data in a table.
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.
But is there a way I can do this by just combining with a select statement and avoiding the loop?
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.
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
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:
@SpecificDogNameId DogName 99 Astro
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
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>
Nesting User-defined Functions of XML Type
User-defined functions such as the above udfGetLowestLevelNestedNode_SpecificDogName can be nested to provide a powerful method to produce complex 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)
<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
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)
<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屋！
