Replace XML node element with a variable

Question

I currently have the following code creating XML under parent nodes named PARTY. Inside the PARTY nodes is another node called ROLE. My question is how can I replace the current ROLE node which just says ROLE with my [SequenceNr] variable inside the xquery.

I had attempted doing a replace in the @OutputXml but that didn't work, and I think there should be an easy way in the xquery that I do not know about.

Current code:

WITH [PartyNodes] AS
        (
            SELECT  (   
                        SELECT  [dbo].[XmlA](@Id),
                                [dbo].[XmlB](@Id)
                        FOR XML PATH(''),TYPE
                    ) AS [AllTogether]
        )
        ,[NumberedSequences] AS
        (
            SELECT  'PARTY' + CONVERT(NVARCHAR, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) + 10) + '_ROLE1' AS [PartySequenceNr], -- party label
                    ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) + 10 AS [SequenceNr], -- party sequence
                    [T].[Party].[query]('.') AS [TheNode]
            FROM [PartyNodes]
            CROSS APPLY [AllTogether].[nodes]('/PARTY') AS T(Party)
        )
        SELECT @OutputXml = (   SELECT [TheNode].[query]('let $p:=/PARTY[1]
                                                      let $lbl:=sql:column("PartySequenceNr")
                                                      let $nr:=sql:column("SequenceNr")
                                                      return
                                                     <PARTY SequenceNumber="{$nr}" xlink_label="{$lbl}" >
                                                     {$p/*}
                                                    </PARTY>'
                                                )
                                FROM [NumberedSequences]
                                FOR XML PATH(''),ROOT('PARTIES')
                                );

Current output:

<PARTY SequenceNumber="1" xlink:label="PARTY1_ROLE1">
    <INDIVIDUAL>
      <NAME>
         <FullName>Test</FullName>
       </NAME>
    </INDIVIDUAL>
    <ROLES>
       <ROLE>
            <A>Test</A>
       </ROLE>
    </ROLES>
</PARTY>

Desired output:

<PARTY SequenceNumber="1" xlink:label="PARTY1_ROLE1">
    <INDIVIDUAL>
      <NAME>
         <FullName>Test</FullName>
       </NAME>
    </INDIVIDUAL>
    <ROLES>
       <ROLE SequenceNumber="1" xlink:label="PARTY1_ROLE1">>
            <A>Test</A>
       </ROLE>
    </ROLES>
</PARTY>

Answer 1

I tried to follow your thoughts, but couldn't...

There are two functions XmlA and XmlB. First you combine their results into AllTogether. Later you use .nodes('/PARTY') to read from this, but the XML you show has got just one single PARTY node as root.

Further more you use FLWOR-XQuery to insert your two attributes. But I doubt, that xlink_label would ever show up as xlink:label.

The XML you show cannot be the result of the above... Or I'm missing something...

My first thought is: This seems to be over complicated... If the following does not help, please provide more details about your goal, try to set up a MCVE. This might be the XY-problem

I start with the XML without attributes, assuming this is the result of the fore going:

DECLARE @xml XML=
N'<PARTY>
    <INDIVIDUAL>
      <NAME>
         <FullName>Test</FullName>
       </NAME>
    </INDIVIDUAL>
    <ROLES>
       <ROLE>
            <A>Test</A>
       </ROLE>
    </ROLES>
</PARTY>';

--This query will insert the attributes in PARTY and in ROLE

DECLARE @PartySequenceNr INT = 1;
DECLARE @SequenceNr VARCHAR(100)='PARTY1_ROLE1';

SELECT @xml.query
(N' 
    let $p:=/PARTY[1]
    let $lbl:=sql:variable("@PartySequenceNr")
    let $nr:=sql:variable("@SequenceNr")
    return
    <PARTY SequenceNumber="{$nr}" xlink_label="{$lbl}" >
    {
        for $nd in $p/*
        return
            if(local-name($nd)="ROLES") then
            ( 
                <ROLES><ROLE SequenceNumber="{$nr}" xlink_label="{$lbl}" >
                {$nd/ROLE/*}
                </ROLE></ROLES>
            )
            else $nd
    }
    </PARTY>
') 

The result:

<PARTY SequenceNumber="PARTY1_ROLE1" xlink_label="1">
  <INDIVIDUAL>
    <NAME>
      <FullName>Test</FullName>
    </NAME>
  </INDIVIDUAL>
  <ROLES>
    <ROLE SequenceNumber="PARTY1_ROLE1" xlink_label="1">
      <A>Test</A>
    </ROLE>
  </ROLES>
</PARTY>

With namespace xlink

Use this if you need the namespace

SELECT @xml.query
(N' declare namespace xlink="DummyUrl";
    let $p:=/PARTY[1]
    let $lbl:=sql:variable("@PartySequenceNr")
    let $nr:=sql:variable("@SequenceNr")
    return
    <PARTY SequenceNumber="{$nr}" xlink:label="{$lbl}" >
    {
    for $nd in $p/*
    return
        if(local-name($nd)="ROLES") then
        ( 
            <ROLES><ROLE SequenceNumber="{$nr}" xlink:label="{$lbl}" >

            {$nd/ROLE/*}

            </ROLE></ROLES>
        )
        else $nd
    }
    </PARTY>
')

The result

<PARTY SequenceNumber="PARTY1_ROLE1" xmlns:xlink="DummyUrl" xlink:label="1">
  <INDIVIDUAL>
    <NAME>
      <FullName>Test</FullName>
    </NAME>
  </INDIVIDUAL>
  <ROLES>
    <ROLE SequenceNumber="PARTY1_ROLE1" xlink:label="1">
      <A>Test</A>
    </ROLE>
  </ROLES>
</PARTY>