sql - 在多个函数之间创建序列

Question

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

我有各种创建 xml 数据的功能，每个功能都可以创建多组“派对”节点。所有函数都从同一个父节点开始。我希望输出看起来像下面这样，其中每一方，无论它来自什么函数，都有连续的序列号。期望的输出:

<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 ALL 连接，它就可以工作。然而，由于所有各方都在不同的功能中，它每次都会重新启动到 PARTY_1。

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

因此，例如，如果我用 1 个通用函数替换 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;