在多个函数之间创建一个序列

Question

问题:如何在多个函数之间创建序列？

我有各种创建xml数据的函数，每个函数都可以创建多个"Party“节点集。所有函数都从同一个父节点开始。我希望输出如下所示，其中每一方，不管它来自哪个函数，都有连续的序列号。期望产出：

<PARTIES>
   <PARTY SequenceNumber="1" label="PARTY_1">
   ...
   <PARTY SequenceNumber="2" label="PARTY_2">
   ...
   <PARTY SequenceNumber="3" label="PARTY_3">
   ...
</PARTIES>

现在，我通过一个返回xml的函数输出我的xml，并且我想要排序的函数被分组在PARTIES节点下面：

SELECT  [dbo].[GetFunction1Xml](@Id),
        [dbo].[GetFunction2Xml](@Id),
        [dbo].[GetFunction3Xml](@Id)
FOR XML PATH(''), ROOT('PARTIES'), TYPE

每个函数从不同的地方收集信息，如下所示：

ALTER GetFunction1XML
...
RETURNS XML (
SELECT  [label] = 'PARTY_' + CONVERT(NVARCHAR,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)))  
        [Var1] = ....,
        [Var2] = ....,
FROM [Table]
FOR XML PATH('PARTY'), TYPE)
END;

我试图使用一个序列，但是在用户定义的函数中是不允许的。

CREATE SEQUENCE Party_Seq
AS INTEGER
START WITH 1
INCREMENT BY 1
MINVALUE 1
NO CYCLE; 

我还在每个函数中尝试了以下内容，因为如果要在同一个函数中有两个当事方由UNION连接，它就可以工作。然而，由于各方都有不同的功能，所以每次都重新启动PARTY_1。

SELECT  [@label] = 'PARTY_' + CONVERT(NVARCHAR,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)))

因此，例如，如果我用一个通用函数替换2个函数，它看起来会是这样，它正确地打印出信息；但是，我有太多的函数可以完成这个任务。

ALTER GetGenericFunctionXML
...
RETURNS XML (

SELECT  [@seq] = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) 
        [@label] = 'PARTY_' + CONVERT(NVARCHAR,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)))  
        [Var1] = [food].[fruit],
        [Var2] = [food].[meat]

FROM ( SELECT 'Apple' AS [fruit],
              'Bacon' AS [meat]
        FROM [Table1]

       UNION ALL

       SELECT 'Grape',
              'Pork'
        FROM [Table2] 
     ) AS [food]

FOR XML PATH('PARTY'), TYPE)
END;

输出：

 <PARTIES>
   <PARTY SequenceNumber="1" label="PARTY_1">
     <Var1>Apple</Var1>
     <Var2>Bacon</Var2>
   <PARTY SequenceNumber="2" label="PARTY_2">
     <Var1>Grape</Var1>
     <Var2>Pork</Var2>
   <PARTY SequenceNumber="3" label="PARTY_3">
 </PARTIES>

我也尝试将一个参数传递给函数，但是由于它们是函数，所以它们不能输出值(我相信只有存储过程才能这样做。如果我错了，请纠正我。)

Answer 1

您可以用FLWOR来解决这个问题。

CREATE FUNCTION dbo.f1() RETURNS XML AS
BEGIN
    RETURN 
    '<PARTY label="PARTY_f1a">
          <Var1>f1a.1</Var1>
          <Var2>f1a.2</Var2>
     </PARTY>
     <PARTY label="PARTY_f1b">
          <Var1>f1b.1</Var1>
          <Var2>f1b.2</Var2>
     </PARTY>
     <PARTY label="PARTY_f1c">
          <Var1>f1c.1</Var1>
          <Var2>f1c.2</Var2>
     </PARTY>';
END
GO
CREATE FUNCTION dbo.f2() RETURNS XML AS
BEGIN
    RETURN 
    '<PARTY label="PARTY_f2a">
          <Var1>f2a.1</Var1>
          <Var2>f2a.2</Var2>
     </PARTY>
     <PARTY label="PARTY_f2b">
          <Var1>f2b.1</Var1>
          <Var2>f2b.2</Var2>
     </PARTY>';
END
GO

--查询从这里开始

WITH AllPartyNodes AS
(
    SELECT
        (
        SELECT dbo.f1()
              ,dbo.f2()
        FOR XML PATH(''),TYPE
        ) AS AllTogether
)
,NumberedSequences AS
(
    SELECT  ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS SequenceNr
           ,The.Party.query('.') AS TheNode
    FROM AllPartyNodes
    CROSS APPLY AllTogether.nodes('/PARTY') AS The(Party)
)
SELECT TheNode.query('let $p:=/PARTY[1]
                      let $lbl:=$p/@label
                      let $nr:=sql:column("SequenceNr")
                      return
                         <PARTY seq="{$nr}" label="{$lbl}" >
                         {$p/*}
                         </PARTY>'
                        ) AS [node()]
FROM NumberedSequences
FOR XML PATH(''),ROOT('PARTIES')

GO
DROP FUNCTION dbo.f1;
DROP FUNCTION dbo.f2;

更新另一种方法

您可以提取数据并重新构建它。

把这个放在我的"NumberedSequence“CTE下面

,TheData AS
(
    SELECT *
          ,TheNode.value('(PARTY/@label)[1]','nvarchar(max)') AS Label
          ,TheNode.query('PARTY/*') AS InnerNodes 
    FROM NumberedSequences
)
SELECT SequenceNr AS [@seq]
      ,Label AS [@label]
      ,InnerNodes AS [node()]
FROM TheData
FOR XML PATH('PARTY'),ROOT('PARTIES')

更新2

与主查询的函数相同。

CREATE FUNCTION dbo.f1() RETURNS XML AS
BEGIN
    RETURN 
    '<PARTY label="PARTY_f1a">
          <Var1>f1a.1</Var1>
          <Var2>f1a.2</Var2>
     </PARTY>
     <PARTY label="PARTY_f1b">
          <Var1>f1b.1</Var1>
          <Var2>f1b.2</Var2>
     </PARTY>
     <PARTY label="PARTY_f1c">
          <Var1>f1c.1</Var1>
          <Var2>f1c.2</Var2>
     </PARTY>';
END
GO
CREATE FUNCTION dbo.f2() RETURNS XML AS
BEGIN
    RETURN 
    '<PARTY label="PARTY_f2a">
          <Var1>f2a.1</Var1>
          <Var2>f2a.2</Var2>
     </PARTY>
     <PARTY label="PARTY_f2b">
          <Var1>f2b.1</Var1>
          <Var2>f2b.2</Var2>
     </PARTY>';
END
GO

--The main query as function
CREATE FUNCTION dbo.f3() RETURNS XML AS
BEGIN
DECLARE @Result XML;

WITH AllPartyNodes AS
(
    SELECT
        (
        SELECT dbo.f1()
              ,dbo.f2()
        FOR XML PATH(''),TYPE
        ) AS AllTogether
)
,NumberedSequences AS
(
    SELECT  ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS SequenceNr
           ,The.Party.query('.') AS TheNode
    FROM AllPartyNodes
    CROSS APPLY AllTogether.nodes('/PARTY') AS The(Party)
)
SELECT @Result=
(
    SELECT TheNode.query('let $p:=/PARTY[1]
                          let $lbl:=$p/@label
                          let $nr:=sql:column("SequenceNr")
                          return
                             <PARTY seq="{$nr}" label="{$lbl}" >
                             {$p/*}
                             </PARTY>'
                            ) AS [node()]
    FROM NumberedSequences
    FOR XML PATH(''),ROOT('PARTIES'), TYPE
)
RETURN @Result;
END
GO

SELECT dbo.f3();
GO

DROP FUNCTION dbo.f1;
DROP FUNCTION dbo.f2;
DROP FUNCTION dbo.f3;