use variable in xpath for sql server

Question

I have an XML like below:

<BODY>
    <RECORD>
        <PA0002_NATIO>CH</PA0002_NATIO>
        <PA0001_CITY>Lugano</PA0001_CITY>
        <PA0005_VALUE>1000</PA0005_VALUE>
    </RECORD>
    <RECORD>
        <PA0002_NATIO>DE</PA0002_NATIO>
        <PA0001_CITY>Berlin</PA0001_CITY>
        <PA0005_VALUE>2000</PA0005_VALUE>
    </RECORD>
    <RECORD>
        <PA0002_NATIO>IT</PA0002_NATIO>
        <PA0001_CITY>Roma</PA0001_CITY>
        <PA0005_VALUE>3000</PA0005_VALUE>
    </RECORD>
</BODY>

I would like to change the value for the tag <PA0002_NATIO> within all <RECORD> nodes and in order to do that I count the number of the <RECORD> nodes and I do a loop like this, the new value is taken from a table.

if @countNodes > 0
    begin
    set @indexCount = 1
    while @indexCount <= @countNodes
        begin           

            -- get the value from the node          
            set @nodevalue = (@xml.value('(//RECORD[sql:variable("@indexCount")]/PA0002_NATIO/text())[1]', 'nvarchar(50)'))

            -- find in the table the value to be replaced
            set @repvalue = (select [Target Code] from [Ronal].[dbo].['Value Mapping$']
            where [List Name]='Nationality' and [SAP Code]=@nodevalue)

            -- replace the value in the node
            set @xml.modify('
            replace value of 
                (//RECORD[sql:variable("@indexCount")]/PA0002_NATIO/text())[1] 
            with
                sql:variable("@repvalue")
            ');


            SET @Indexcount=  @Indexcount + 1;
        end
end

END

now the idea is to make a generic replace using a variable in the xpath instead of using

set @nodevalue = (@xml.value('(//RECORD[sql:variable("@indexCount")]/PA0002_NATIO/text())[1]', 'nvarchar(50)'))

I would use

set @nodevalue = (@xml.value('(//RECORD[sql:variable("@indexCount")]/[sql:variable("@tag")]/text())[1]', 'nvarchar(50)'))

and of course I would use same syntax to replace

-- replace the value in the node
set @xml.modify('
replace value of 
    (//RECORD[sql:variable("@indexCount")]/[sql:variable("@tag")]/text())[1] 
with
    sql:variable("@repvalue")
');

Where the @tag variable contains <PA0002_NATIO> but also <PA0001_CITY> and so on getting the data from another table that store the tag name.

How can I do this?

Instead of sql:variable you can join table you need and use sql:column — gofr1
– gofr1, Commented Sep 8, 2016 at 12:57
Can you show what output you need to get from above XML? Why you say that you dont have a table if you use this to get value to update: select [Target Code] from [Ronal].[dbo].['Value Mapping$'] where [List Name]='Nationality' and [SAP Code]=@nodevalue — gofr1
– gofr1, Commented Sep 8, 2016 at 13:18
the output should be like the initial xml where instead of having CH i need to have 00 for DE i need 01 and for IT i need 02. these value are picked up from a table where the table has 2 column one contains CH and the other 00 same for other rows DE and IT. so i enter in the table with the value that i get from the xml — zanza67
– zanza67, Commented Sep 8, 2016 at 13:26

gofr1 · Accepted Answer · 2016-09-08 14:12:24Z

1

You need a tricky .modify with loop:

--declare table with names and ids like you posted in comment
DECLARE @test TABLE (
    Name nvarchar(2),
    id nvarchar(2)
)

INSERT INTO @test VALUES
('CH', '00'),
('DE', '01'),
('IT', '02')


DECLARE @xml XML = '
    <BODY>
            <RECORD>
                <PA0002_NATIO>CH</PA0002_NATIO>
                <PA0001_CITY>Lugano</PA0001_CITY>
                <PA0005_VALUE>1000</PA0005_VALUE>
            </RECORD>
            <RECORD>
                <PA0002_NATIO>DE</PA0002_NATIO>
                <PA0001_CITY>Berlin</PA0001_CITY>
                <PA0005_VALUE>2000</PA0005_VALUE>
            </RECORD>
            <RECORD>
                <PA0002_NATIO>IT</PA0002_NATIO>
                <PA0001_CITY>Roma</PA0001_CITY>
                <PA0005_VALUE>3000</PA0005_VALUE>
            </RECORD>
    </BODY>';

DECLARE @Counter int = 1,
        @newValue nvarchar(max),
        @nodename nvarchar(max) ='PA0002_NATIO'

WHILE @Counter <= @xml.value('fn:count(//*//*//*[local-name()=sql:variable("@nodename")])','int')
BEGIN
    SELECT @newValue = id
    FROM @test
    WHERE Name = CAST(@xml.query('((/*/*/*[local-name()=sql:variable("@nodename")])[position()=sql:variable("@Counter")]/text())[1]') as nvarchar(2))

    SET @xml.modify('replace value of ((/*/*/*[local-name()=sql:variable("@nodename")])[position()=sql:variable("@Counter")]/text())[1] with sql:variable("@newValue")')

    SET @Counter = @Counter + 1;
END

SELECT  @xml;

Output:

<BODY>
  <RECORD>
    <PA0002_NATIO>00</PA0002_NATIO>
    <PA0001_CITY>Lugano</PA0001_CITY>
    <PA0005_VALUE>1000</PA0005_VALUE>
  </RECORD>
  <RECORD>
    <PA0002_NATIO>01</PA0002_NATIO>
    <PA0001_CITY>Berlin</PA0001_CITY>
    <PA0005_VALUE>2000</PA0005_VALUE>
  </RECORD>
  <RECORD>
    <PA0002_NATIO>02</PA0002_NATIO>
    <PA0001_CITY>Roma</PA0001_CITY>
    <PA0005_VALUE>3000</PA0005_VALUE>
  </RECORD>
</BODY>

edited Sep 8, 2016 at 14:12

answered Sep 8, 2016 at 13:56

gofr1

16k11 gold badges47 silver badges56 bronze badges

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

6 Comments

zanza67 Over a year ago

thank you so much gofr1 but in your case you hardcode PA0002_NATIO that is what i want to avoid. what i want to achieve is a parameter version of replace where the value to be replaced for the tag depends on the dinamic xpath that i want to build. for example i want to replace value for Record\PA0002_NATIO but also for Record\PA0001_CITY, so in order todo that i need to write twice, my idea is get the PA0002_NATIO and PA0001_CITY somewhere (table for example) and provide at runtime as variable to the xpath in order to replace the value. maybe i didn't explain correctly, sorry about that

gofr1 Over a year ago

Check my edits. @nodename nvarchar(max) ='PA0002_NATIO' in @nodename pass node name you need.

zanza67 Over a year ago

gofr1 instead of using a tagname can i use a a concatenation maybe it is not the right word but in essence i would pass instead of PA0002_NATIO something like RECORD[1]/PA0002_NATIO. i have see that in your case the position has been checked at PA0002_NATIO level instead of RECORD. the reason why is becasue i can have PA0002_NATIO at different level which is not this case but could happen

gofr1 Over a year ago

Of cause you can, but you need to use another variable for RECORD like

'((/*/*[local-name()=sql:variable("@anothernodename")][1]/*[local-name()=sql:variable("@nodename")])[position()=sql:variable("@Counter")]/text())[1]'

zanza67 Over a year ago

is it possible to check the position for the @anothernodename instead of for @nodename?. do i need to shift the check of the position to the first sql:variable? am i wrong?

|

Collectives™ on Stack Overflow

use variable in xpath for sql server

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related