Update XML Column

Question

Update XML Column

GBeezy

SSCarpal Tunnel

Points: 4723
More actions
July 23, 2014 at 1:14 pm

#397105

I have a table "table1" that has an XML column (by the name of VariableData) in it. I need to update one part of this VariableData XML column. The XML looks like the following:
<custid>1234</custid>
<procflag>P</procflag>
<txtfilename>file1.pdf_file2.txt</txtfilename>
<filename>ST_1234_go.ps</filename>
<stmt>987654321</stmt>
<loc>2</loc>
<doc>100</doc>
<acctnum />
<deliverymethod>M</deliverymethod>
<batchid>456321</batchid>
I need to change the <txtfilename> from file1.pdf_file2.txt to be just file2.txt. Below is the UPDATE statement that I am attempting to use:
UPDATE table1
SET VariableData.modify = ('replace value of (/txtfilename)[1] with "file2.txt"')
WHERE ProcessID = 123
and I am getting the following error:
Msg 8173, Level 15, State 1, Line 1
Incorrect syntax was used to invoke the xml data type method 'modify'.
I've been Googling this and trying to get the column updated different ways, but I am not having any luck. If anyone can point me in the right direction on this, I would greatly appreciate it.
Thank You

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

Just a confirmation before I attempt to recreate the issue, please confirm you're actually in 2k5 and didn't just post on the wrong board.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

GBeezy SSCarpal Tunnel Points: 4723 More actions · Answer 2

Evil,

I am indeed on SQL Server 2005.

Thanks for the help!!!

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

It's late but I just wanted to drop a note. You're not forgotten, I'm just overloaded the past two days. I'll try to get to this tomorrow if someone doesn't step in before I can.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 4

Here is a quick solution, should get you passed this hurdle.

😎

USE tempdb;

GO

DECLARE @XML_ID INT;

DECLARE @FILE_NAME VARCHAR(255);

SET @XML_ID = 2;

SET @FILE_NAME = 'text_file.txt';

DECLARE @TXML XML ;

DECLARE @XML_TABLE TABLE (XML_ID INT IDENTITY(1,1) NOT NULL,TXML XML NOT NULL);

SET @TXML = '<custid>1234</custid>

<procflag>P</procflag>

<txtfilename>file1.pdf_file2.txt</txtfilename>

<filename>ST_1234_go.ps</filename>

<stmt>987654321</stmt>

<loc>2</loc>

<doc>100</doc>

<acctnum />

<deliverymethod>M</deliverymethod>

<batchid>456321</batchid>'

INSERT INTO @XML_TABLE (TXML) VALUES (@TXML);

SELECT * FROM @XML_TABLE

UPDATE @XML_TABLE

SET TXML.modify('replace value of (/txtfilename[1]/text())[1]

with sql:variable("@FILE_NAME")')

WHERE XML_ID = @XML_ID

SELECT * FROM @XML_TABLE

GBeezy SSCarpal Tunnel Points: 4723 More actions · Answer 5

Thanks for the replies!!!!

The solution that has been provided doesnt actually update the field. Copying that out into SSMS and running it looking at the resulting XML column, there is no change.

I am attempting to tweak it myself, but I am not having much luck.

Thanks Again...

GBeezy SSCarpal Tunnel Points: 4723 More actions · Answer 6

I actually got it to work...

I took just the UPDATE statement you provided, took out the variables, input what I needed, and I was able to update my column as I needed.

This is sufficient for me!!!

Thanks for the help.

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 7

GBeezy (7/25/2014)
I actually got it to work...
I took just the UPDATE statement you provided, took out the variables, input what I needed, and I was able to update my column as I needed.
This is sufficient for me!!!
Thanks for the help.

I was certain you would figure this out, good job!;-)

😎

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 8

Glad you got your answer. Thanks for doing that, Eirikur. 🙂

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA