CODEWITHSUNDEEP
sql-servervariablesxpath
zanza67
zanza67

Reputation: 223

use variable in xpath for sql server

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?

Upvotes: 3

Views: 2837

Answers (1)

gofr1
gofr1

Reputation: 15997

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>

Upvotes: 1

Related Questions