Create a sequence between multiple functions

Question

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

You should post sample data and the code from your UDF. Without those it's not really clear what you have going on. I have no idea how you are getting 11, 12 and 13 for your ID's. — btberry
– btberry, Commented Aug 31, 2016 at 0:30
@btberry Those were example values. I have changed them to 1, 2, and 3 above. That is what I want to achieve but I haven't been able to. — DRockClimber
– DRockClimber, Commented Aug 31, 2016 at 1:43
Is there a reason you are using a UDF? A scalar UDF like you have here will always have performance issues and I just don't see what you are doing that makes it desirable or needed. Put that code in in a cte or subquery or something. If there is some reason you really have to do the UDF or if it's just a fun puzzle to try to solve please explain. — btberry
– btberry, Commented Aug 31, 2016 at 2:41
@btberry No there is no reason. I didn't know there were performance issues to it. I would rather do what has better performance and works. So you're saying if I wrap all that inside a cte would be better? — DRockClimber
– DRockClimber, Commented Aug 31, 2016 at 13:51
@btberry I do not agree... There are severe performance issues with multi-statement-table-valued-functions (the syntax with AS BEGIN...END), but a scalar function returning one single XML should be OK. One most be aware, that the calls are not fully inlined and therefore the optimizer might not find the best execution plan. This depends on the kind of data you are pulling for your XML... — Gottfried Lesigang
– Gottfried Lesigang, Commented Aug 31, 2016 at 13:56

Gottfried Lesigang · Accepted Answer · 2016-08-31 16:56:48Z

2

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;

edited Aug 31, 2016 at 16:56

answered Aug 31, 2016 at 13:53

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

DRockClimber Over a year ago

I tried running this but I get a couple of errors. In NumberedSequences [AllTogether] is an invalid object name. Isn't it because it's outside the scope of the previous SELECT?

Gottfried Lesigang Over a year ago

@DRockClimber, did you copy the whole lot, function creation and queries?

DRockClimber Over a year ago

I ran your query in a new query window and it worked. Then I put it inside an Alter Function and it created a lot of errors. I think it has to do with that. I have Returns XML AS BEGIN RETURN (With AllPartyNodes AS... and this creates an error in the With statement. @Shnugo

DRockClimber Over a year ago

Wow thanks it worked! Now I just have to build on that. Hey I've been looking for what the $ as well as the := operator does but I can't seem to find it besides the link you posted. I want to understand what those do. Do you have any other documentation for it?

DRockClimber Over a year ago

Forgot to tag in above. @Shnugo

|

Collectives™ on Stack Overflow

Create a sequence between multiple functions

1 Answer 1

UPDATE Another approach

UPDATE 2

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

UPDATE Another approach

UPDATE 2

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related