DRockClimber DRockClimber - 3 months ago 14
SQL Question

Create a sequence between multiple functions

QUESTION: How to create a sequence between multiple functions?

I have various functions which create xml data and each function can create multiple sets of "Party" nodes. All of the functions start of with the same parent node. I want the output to look like the following where each party regardless of what function it comes from has the continuing sequence number. DESIRED OUTPUT:

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


Right now I am outputting my xml through a function that returns xml, and the functions I want to sequence are grouped together under PARTIES node:

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


Each function collects information from different places and could look like this:

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;


I attempted to use a Sequence however it is not allowed in user-defined functions.

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


I also attempted the following inside each function since it works if I were to have two parties in the same function connected by a UNION ALL. However it restarts to PARTY_1 everytime since all the parties are in different functions.

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


So for example if i were to replace 2 functions with 1 generic one it would look like this and it print out the information correctly; however I have way too many functions to do this.

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;


Output:

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


I also tried passing a parameter to the functions but since they are functions they can't output the value (I believe only stored procedures can do this. Correct me if I'm wrong.).

Answer

You might solve this with 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

--The query starts here

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;

UPDATE Another approach

You might extract the data and rebuild it.

Put this below my "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')

UPDATE 2

The same with the main query as function

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