Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update XML Column Expand / Collapse
Author
Message
Posted Wednesday, July 23, 2014 1:14 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:39 AM
Points: 128, Visits: 556
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
Post #1595677
Posted Wednesday, July 23, 2014 3:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:08 PM
Points: 5,401, Visits: 7,514
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
Post #1595724
Posted Thursday, July 24, 2014 6:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:39 AM
Points: 128, Visits: 556
Evil,

I am indeed on SQL Server 2005.

Thanks for the help!!!
Post #1595865
Posted Friday, July 25, 2014 12:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:08 PM
Points: 5,401, Visits: 7,514
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
Post #1596093
Posted Friday, July 25, 2014 1:43 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:21 AM
Points: 2,220, Visits: 6,004
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);
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

Post #1596108
Posted Friday, July 25, 2014 9:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:39 AM
Points: 128, Visits: 556
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...
Post #1596294
Posted Friday, July 25, 2014 9:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:39 AM
Points: 128, Visits: 556
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.
Post #1596298
Posted Friday, July 25, 2014 11:49 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:21 AM
Points: 2,220, Visits: 6,004
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!
Post #1596337
Posted Friday, July 25, 2014 12:12 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:08 PM
Points: 5,401, Visits: 7,514
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1596348
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse