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>

Community · Accepted Answer · 2017-05-23 12:00:27Z

2

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>

edited May 23, 2017 at 12:00

CommunityBot

11 silver badge

answered Dec 27, 2016 at 10:02

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

4 Comments

DRockClimber Over a year ago

wow that's awesome. how efficient is this? or how would I test it's performance. I actually ended up doing this a different way. I created a table type, where I inserted my labels and some other information and then I am passing that variable type to my party functions and selecting the specific record from there.

Gottfried Lesigang Over a year ago

@DRockClimber, you can test the performance basically with DECLARE @d DATETIME=GETDATE(); DoSomething; SELECT CAST(GETDATE()-@d AS TIME); But this approach is - for sure - not best performing... Creating the XML just to press it through some FLWOR-XQuery must be slow... Your whole thing smells over complicated, but I don't know any details. As pointed out, even the provided information is inconsistent...

DRockClimber Over a year ago

Can you clarify something for me. Where are you specifying that $nd holds the value of ROLES before you check for it in the if condition?

Gottfried Lesigang Over a year ago

@DRockClimber This line is responsible for the value of $nd: for $nd in $p/* This will read all sub-nodes from $p. Working down the for-loop will shift each sub-node into $nd, one after the other. The node ROLES needs a different handling, therefore the if checking with local-name().

Collectives™ on Stack Overflow

Replace XML node element with a variable

1 Answer 1

With namespace `xlink`

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

With namespace xlink

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related

With namespace `xlink`