Updating xml value in SQL text column

SQL To update a text value with xml inside of the column.

For example...and please note...the node I need to change will never be in the same order.....and will not have the same date value.

<DATEPASSCHANGE>08/09/2012</DATEPASSCHANGE><CA_FILTERFILELISTFIRST>NO</CA_FILTERFILELISTFIRST><PREVIOUSLASTLOGIN>1/17/2018</PREVIOUSLASTLOGIN>

Here is the example.  Note I need to change the DATEPASSCHANGE value to todays date....regardless of the date.  

So I guess I need some query that will find the node...as it could be anywhere in the string....and then take whatever the value is and make todays date.

Please help...I only know how to do this with one value if I knew what the value I was changing was and I have thousands of these records in different orders with different dates.


Thanks
LVL 11
Robb HillSenior .Net DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
There is no secure way to do it in a text column.. why not storing it in a XML column??

E.g.

DECLARE @BadSample TABLE
    (
        ID INT IDENTITY ,
        TextData NVARCHAR(MAX)
    );

INSERT INTO @BadSample ( TextData )
VALUES ( N'<DATEPASSCHANGE>08/09/2012</DATEPASSCHANGE><CA_FILTERFILELISTFIRST>NO</CA_FILTERFILELISTFIRST><PREVIOUSLASTLOGIN>1/17/2018</PREVIOUSLASTLOGIN>' );

DECLARE @XmlHelper TABLE
    (
        ID INT ,
        XmlData XML
    );

INSERT INTO @XmlHelper ( ID ,
                         XmlData )
            SELECT BS.ID ,
                   CAST(BS.TextData AS XML) AS XmlData
            FROM   @BadSample BS;

DECLARE @GetDate DATE = GETDATE();

UPDATE @XmlHelper
SET    XmlData.modify('replace value of (/DATEPASSCHANGE/text())[1] with sql:variable("@GetDate") ');

SELECT XH.ID ,
       XH.XmlData
FROM   @XmlHelper XH;

Open in new window

0
Scott PletcherSenior DBACommented:
Don't know your actual table name, so I used #table_name; just change that to your actual table name.

UPDATE tn
SET column_name = /* CAST( --if needed */
     STUFF(CAST(tn.column_name AS varchar(max)), value_start + 16, value_length,
        CONVERT(varchar(10), GETDATE(), 101))
    /* AS text) */
FROM #table_name tn
CROSS APPLY (
    SELECT CHARINDEX('<DATEPASSCHANGE>', CAST(tn.column_name AS varchar(max))) AS value_start
) AS alias1
CROSS APPLY (
    SELECT CHARINDEX('</DATEPASSCHANGE>', CAST(tn.column_name AS varchar(max)), value_start + 10) AS value_end
) AS alias2
CROSS APPLY (
    SELECT value_end - value_start - 16 AS value_length
) AS alias3
WHERE tn.column_name LIKE '%<DATEPASSCHANGE>%'
 
SELECT TOP (100) * FROM #table_name WHERE tn.column_name LIKE '%<DATEPASSCHANGE>%'


/*

CREATE TABLE #table_name (
    column_name varchar(max) NULL
    );

INSERT INTO #table_name VALUES('<DATEPASSCHANGE>08/09/2012</DATEPASSCHANGE><CA_FILTERFILELISTFIRST>NO</CA_FILTERFILELISTFIRST><PREVIOUSLASTLOGIN>1/17/2018</PREVIOUSLASTLOGIN>')

INSERT INTO #table_name VALUES('<DUMMYTAGTOTAKEUPSPACE>abcdef</DUMMYTAGTOTAKEUPSPACE><DATEPASSCHANGE>08/09/2012</DATEPASSCHANGE><CA_FILTERFILELISTFIRST>NO</CA_FILTERFILELISTFIRST><PREVIOUSLASTLOGIN>1/17/2018</PREVIOUSLASTLOGIN>')

*/
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ste5anSenior DeveloperCommented:
<DATEPASSCHANGE> and <DATEPASSCHANGE ID="123"> are the same element..
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Arifhusen AnsariBusiness Intelligence Developer and AnalystCommented:
Hello Rob Hill,

As per your explanation about requirement, there are two points. I might be wrong.

1) Data is in String
2) Node can be any where in the xml

We can use direct XML function to replace the data in all the node. If XML is well structure. But as you mentioned , this node can be any where.

You can use below script to do the same.

In this <DATEPASSCHANGE> and <DATEPASSCHANGE ID="123"> will be considered as same. If you don't want them to be considered same, we can changes some logic to achieve that also.

DECLARE @data VARCHAR(2000)=
'<DATEPASSCHANGE>08/09/2012</DATEPASSCHANGE><CA_FILTERFILELISTFIRST>NO</CA_FILTERFILELISTFIRST><PREVIOUSLASTLOGIN>1/17/2018</PREVIOUSLASTLOGIN><SubTest><DATEPASSCHANGE>08/10/2012</DATEPASSCHANGE></SubTest><test><a><DATEPASSCHANGE ID="123">08/10/2012</DATEPASSCHANGE></a></test>'



DECLARE @replaceto VARCHAR(10)='08/12/2012'
DECLARE @nodetofind VARCHAR(200)='DATEPASSCHANGE'
DECLARE @finlaNodeToSearch VARCHAR(400)=CONCAT('<',@nodetofind)

;WITH CTE
AS

(
      SELECT STUFF(
						@data
						,CHARINDEX(@finlaNodeToSearch,@data,1)+CHARINDEX('>',@data,CHARINDEX(@finlaNodeToSearch,@data,1))
						,10
						,@replaceto
					) AS NewString
				, CHARINDEX(@finlaNodeToSearch,@data)+1 AS New_Point
      UNION ALL

      SELECT 
				STUFF(
						NewString
						,CHARINDEX(@finlaNodeToSearch,NewString,New_Point)+CHARINDEX('>',NewString,CHARINDEX(@finlaNodeToSearch,NewString,New_Point))-CHARINDEX(@finlaNodeToSearch,NewString,New_Point)+1
						,10
						,@replaceto
				) AS NewString
				, CHARINDEX(@finlaNodeToSearch,NewString,New_Point)+1 AS New_Point
      FROM CTE
      WHERE CHARINDEX(@finlaNodeToSearch,NewString,New_Point)<>0
      

)


SELECT 
	 TOP 1 NewString
FROM CTE 
ORDER BY New_Point DESC

Open in new window

0
ste5anSenior DeveloperCommented:
<DATEPASSCHANGE ID="123">08/09/2012</DATEPASSCHANGE>
<SomeCData><![CDATA[<DATEPASSCHANGE>]]</SomeCData>

Open in new window


is also valid XML..
0
Robb HillSenior .Net DeveloperAuthor Commented:
what is the ID= 123....not sure where you all are getting that from..
0
ste5anSenior DeveloperCommented:
XML elements definition.. they can contain attributes. And the OP clearly stated, that there's no schema. And honestly, XML parsing by hand is as good as CSV parsing by hand..
0
Robb HillSenior .Net DeveloperAuthor Commented:
Keep in mind this is an update of some 922 rows ...that has one text column in each row...that has that node.

I need to find node.....it will exist as I make sure of that in a where clause.

And then replace the date value with 02-28-2018.

Does this do that?
0
ste5anSenior DeveloperCommented:
Sure, just use any version. But keep in mind, that XML allows a lot of possibilites how the data can be structured, which are only covered by using .modify().
0
Robb HillSenior .Net DeveloperAuthor Commented:
I dont have the ability to change the column of data.  I have to use it how it is.   I do wish this was not a text field as well.

I just need to get an update statement....
0
Robb HillSenior .Net DeveloperAuthor Commented:
This select statement returns me 3224 records of this text field which has the xml like data.

select msettings from cadoc_system..tSubscr

where cSiteCode != 'Root' and  cUserID NOT LIKE 'Template_%' and lActive = 1
and  msettings  like '%<DATEPASSCHANGE>%'

Open in new window


So I am not seeing any the above responses how I would take a query like this...and change it to an update that would essentially put any data in the DATEPASSCHANGE node to today's date.
0
Robb HillSenior .Net DeveloperAuthor Commented:
going to Scotts update I did the following..still trying to test as a select..but getting error on the Stuff

UPDATE tn
SET msettings = /* CAST( --if needed */
     STUFF(CAST(tn.msettings AS varchar(max)), value_start + 16, value_length, 
        CONVERT(varchar(10), GETDATE(), 101))
    /* AS text) */
FROM cadoc_system..tSubscr tn
CROSS APPLY (
    SELECT CHARINDEX('<DATEPASSCHANGE>', CAST(tn.msettings AS varchar(max))) AS value_start
) AS alias1
CROSS APPLY (
    SELECT CHARINDEX('</DATEPASSCHANGE>', CAST(tn.msettings AS varchar(max)), value_start + 10) AS value_end
) AS alias2
CROSS APPLY (
    SELECT value_end - value_start - 16 AS value_length
) AS alias3
where cSiteCode != 'Root' and  cUserID NOT LIKE 'Template_%' and lActive = 1
and  msettings  like '%<DATEPASSCHANGE>%'

Open in new window

0
Robb HillSenior .Net DeveloperAuthor Commented:
Thank you all so very much!   That was perfect
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.