SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Update XML Column


Update XML Column

Author
Message
GBeezy
GBeezy
Old Hand
Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)

Group: General Forum Members
Points: 311 Visits: 711
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
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8555 Visits: 7660
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
GBeezy
GBeezy
Old Hand
Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)

Group: General Forum Members
Points: 311 Visits: 711
Evil,

I am indeed on SQL Server 2005.

Thanks for the help!!!
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8555 Visits: 7660
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Eirikur Eiriksson
Eirikur Eiriksson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14916 Visits: 18591
Here is a quick solution, should get you passed this hurdle.
Cool

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);
INSERT INTO @XML_TABLE (TXML) VALUES (@TXML);
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
GBeezy
Old Hand
Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)

Group: General Forum Members
Points: 311 Visits: 711
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
GBeezy
Old Hand
Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)

Group: General Forum Members
Points: 311 Visits: 711
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
Eirikur Eiriksson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14916 Visits: 18591
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!;-)
Cool
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8555 Visits: 7660
Glad you got your answer. Thanks for doing that, Eirikur. Smile


- 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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search