T-SQL 2014 XQuery solution to using substring

Question

I'm trying to write an update statement to an XML column in a table that is formatted like this.

this is in on field of of a table

 <Params>
        <Account>
            <FirstName>Michael</FirstName>
            <LastName>Bar</LastName>
        </Account>
        <Account>
            <FirstName>Pam</FirstName>
            <LastName>Foo</LastName>
        </Account>
    </Params>




**this is in on field of ONE of another record in that table
    <Params>
        <Account>
            <FirstName>Darold</FirstName>
            <LastName>ok</LastName>
        </Account>
        <Account>
            <FirstName>Fred</FirstName>
            <LastName>whatever</LastName>
        </Account>
        <Account>
             <FirstName>amy</FirstName>
             <LastName>whatever</LastName>
    </Account>
    </Params>
**

Desired output

    <Params>
        <Account>
            <FirstName>Michael2334</FirstName>
            <LastName>Bar2334</LastName>
        </Account>
        <Account>
            <FirstName>Pam2334</FirstName>
            <LastName>Foo2334</LastName>
        </Account>
    </Params>





    <Params>
        <Account>
            <FirstName>Darold2335</FirstName>
            <LastName>ok2335</LastName>
        </Account>
        <Account>
            <FirstName>Fred2335</FirstName>
            <LastName>whatever2335</LastName>
        </Account>
        <Account>
            <FirstName>amy2335</FirstName>
            <LastName>whatever2335</LastName>
        </Account>
    </Params>

My ultimate objective is to update the entire table's xml field, which has nth amount of nodes with a joined tables ID column, and append that ID to the First and Last Name

the "specs" are to update the first name and put the id (integer) column at the end of the last name and first name (which i have solved in a PoC from the SQLFiddle below, but not fully as it only works if there is one record in the table, and only tried firstname for now)

I SPENT ALL DAY

I have looked over the internet and the function modify(replace value of...) can only effect 1 of the accounts at a time. Because of this, I had to create a loop like so. But its very ugly and the bottom variable KeepGoing would have worked so much better if SQL Server would support the XPath function matches() and could check for the existence of a digit with regular expressions but this is not in SQL Server 2017. My work around is going to use contains() 10 times! To see if I'm done and can move on to the next record.

If anyone has a better elegant solution please let me know. The person who is requesting this wants this is all one statement and no cursors. Which I already from the code below have gone of course and given up.

1) What would be the query to return from the below XML that would return

4
4

4 for the last character in FirstName

and

4 for the last character in FirstName in the second node

this select should also return

5

for the next record

But this is not the MAIN problem, this is a fyi trying to use substring and in xpath

im trying to evaluate via a while loop so i know when to stop executing on 1 by looking for an integer on the last character, i have failed trying to write below

SELECT XMLCol.value('//Account/FirstName/text()[substring(., string-length(x)-1,11)]', 'nvarchar(255)) FROM XMLtbl

I have been trying all formations of below only to get this messages like...

Msg 2203, Level 16, State 1, Line 114
XQuery [XMLtbl.XMLCol.value()]: Only 'http://www.w3.org/2001/XMLSchema#decimal?', 'http://www.w3.org/2001/XMLSchema#boolean?' or 'node()*' expressions allowed as predicates, found 'xs:string'

Here is everything I'm working on

this is a little bit less then making SQL string and dynamically running the SQL using EXEC, just trying to do it the best way possible.

http://sqlfiddle.com/#!18/7eed1

Much obliged I knew xquery/xpath was nothing easy, and stayed away from it and left it for the experts

The Final fiddle (based on answer below, but it has a few more fields that i use that are irrelevant to the question)

http://sqlfiddle.com/#!18/afff6

Shnugo Shnugo · Accepted Answer · 2018-04-26T07:24:49

Your question is quite long, almost TL;DR, but still not very clear... It would help to provide the expected output...

Some asumptions and statements:

Yes, .modify() allows for one single change per call - quite a limitation...
You want to update your XML on several places
From your specs you want to add the ID into the XML
Don't know what you want to achieve with the last letter approach

However, in your case it seems best to shred the XML and re-create it from scratch. You can use any T-SQL functionality for this:

DECLARE @tbl TABLE(ID INT IDENTITY, YourXML XML);
INSERT INTO @tbl VALUES
(N'<Params>
    <Account>
        <FirstName>Michael</FirstName>
        <LastName>Bar</LastName>
    </Account>
    <Account>
        <FirstName>Pam</FirstName>
        <LastName>Foo</LastName>
    </Account>
</Params>')
,(N'<Params>
    <Account>
        <FirstName>John</FirstName>
        <LastName>Wood</LastName>
    </Account>
    <Account>
        <FirstName>Jane</FirstName>
        <LastName>Smith</LastName>
    </Account>
    <Account>
        <FirstName>Jim</FirstName>
        <LastName>Stone</LastName>
    </Account>
</Params>');

-- The query uses an "updateable CTE" to create the value, which can be used in the UPDATE afterwards.

WITH cte AS
(
    SELECT t.YourXML
          ,(
            SELECT 
                  --just silly, to show how it works: append "blah" to the first name
                   Acc.value(N'(FirstName/text())[1]',N'nvarchar(max)') + '_blah' AS FirstName
                   --Just pick the last character as LastName
                  ,RIGHT(Acc.value(N'(LastName/text())[1]',N'nvarchar(max)'),1) AS LastName
                  --Add the ID in the last place
                  ,t2.ID
            FROM @tbl AS t2
            CROSS APPLY t2.YourXML.nodes(N'/Params/Account') AS A(Acc)
            WHERE t2.ID=t.ID
            FOR XML PATH('Account'),ROOT('Params'),TYPE
           ) AS NewXML
    FROM @tbl AS t
)
UPDATE cte SET YourXML=NewXML;

SELECT * FROM @tbl;

One of these XMLs would look like this after the action

<Params>
  <Account>
    <FirstName>Michael_blah</FirstName>
    <LastName>r</LastName>
    <ID>1</ID>
  </Account>
  <Account>
    <FirstName>Pam_blah</FirstName>
    <LastName>o</LastName>
    <ID>1</ID>
  </Account>
</Params>

UPDATE

This query will append the row's ID (padded to a five-digit number) to both name parts:

WITH cte AS
(
    SELECT t.YourXML
          ,(
            SELECT 
                   Acc.value(N'(FirstName/text())[1]',N'nvarchar(max)') + REPLACE(STR(t2.ID,5),' ','0') AS FirstName
                  ,Acc.value(N'(LastName/text())[1]',N'nvarchar(max)') + REPLACE(STR(t2.ID,5),' ','0') AS LastName
            FROM @tbl AS t2
            CROSS APPLY t2.YourXML.nodes(N'/Params/Account') AS A(Acc)
            WHERE t2.ID=t.ID
            FOR XML PATH('Account'),ROOT('Params'),TYPE
           ) AS NewXML
    FROM @tbl AS t
)
UPDATE cte SET YourXML=NewXML;

SELECT * FROM @tbl;

And this query will deliver the last character of each name

SELECT Acc.value(N'substring((FirstName/text())[1],string-length((FirstName/text())[1]),1)',N'char(1)')
FROM @tbl AS t
CROSS APPLY t.YourXML.nodes(N'/Params/Account') AS A(Acc);

T-SQL 2014 XQuery solution to using substring

1 Answers

UPDATE