更新时间:2023-02-03 12:51:29
您是否尝试过嵌套 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
最简单,最低级别的嵌套节点示例
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>