Solved

Update text column converting to xml and updating via XPATH!

Posted on 2006-11-21
5
682 Views
Last Modified: 2008-03-04
Ok so I have a requirement to go through a table and copy a node from one place in the xml to another.


The table looks like this
dbo.LLCW18_DOCSET_TB
     FLDR_ID_NR(int, null)
     DOC_SET_XML_CO(text,null)

The DOC_SET_XML_CO is really xml stored as text.  So I have written the following sql

UPDATE dbo.LLCW18_DOCSET_TB
SET DOC_SET_XML_CO =
CASE
      WHEN cast(dbo.LLCW18_DOCSET_TB.DOC_SET_XML_CO as xml).exist('/BizTalk/Body/ConsumerLoan/TransferRequest/DocumentSet/DocumentData/Presentation/GAP/interestRate')=1
                  THEN cast([DOC_SET_XML_CO] as xml).modify('insert ' + cast(cast([DOC_SET_XML_CO] as xml).query('/BizTalk/Body/ConsumerLoan/TransferRequest/DocumentSet/DocumentData/Presentation/GAP/interestRate') as varchar(200))
        + ' after (/BizTalk/Body/ConsumerLoan/TransferRequest/Insurance/Product[@insuranceProductCode="GAP"])[1]')
      else DOC_SET_XML_CO
END


but I get the following error.

Msg 8137, Level 16, State 1, Line 1
Illegal use of xml data type method 'modify'. A non-mutator method is expected in this context.


Can anyone help me with this??

0
Comment
Question by:macros14
  • 2
5 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17987949
u can't update a text field usinf UPDATE command, u need to use WRITETEXT and UPDATETEXT
0
 
LVL 1

Author Comment

by:macros14
ID: 17988113
So would you know how to move a xml node in the text column to a different place the the xml doc stored in the text column?
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 17989354
CREATE TABLE test (i int identity(1,1), j int, t text, c char(2))
go


delete test


INSERT INTO Test SELECT 1, 'asadf', 'I'
INSERT INTO Test SELECT 1, 'jjjjj', 'A'
INSERT INTO Test SELECT 1, 'kkkkksadf', 'A'
DECLARE @t varchar(8000)
SELECT @t = space(6000) + 'sadf'
INSERT INTO Test SELECT 1, @t, 'A'
SELECT @t = 'sadf'
INSERT INTO Test SELECT 1, @t, 'A'
SELECT @t = space(6000) + 'sadf'
INSERT INTO Test SELECT 1, @t, 'A'
SELECT @t = 'sadf'
INSERT INTO Test SELECT 1, @t, 'A'

INSERT INTO Test SELECT 2, 'asadf', 'I'
INSERT INTO Test SELECT 2, 'jjjjj', 'A'
INSERT INTO Test SELECT 2, 'kkkkksadf', 'A'
--DECLARE @t varchar(8000)
SELECT @t = space(6000) + 'sadf'
INSERT INTO Test SELECT 2, @t, 'A'
SELECT @t = 'sadf'
INSERT INTO Test SELECT 2, @t, 'A'
SELECT @t = space(6000) + 'sadf'
INSERT INTO Test SELECT 2, @t, 'A'
SELECT @t = 'sadf'
INSERT INTO Test SELECT 2, @t, 'A'






DECLARE @old varchar(20) ,
 @new varchar(20)
SELECT  @old = 'adf' ,
  @new = 'qsd'
 
DECLARE @i int

DECLARE @txtPtr binary(16) ,
 @offset int ,
 @dellen int
 
SELECT @dellen = len(@old)
SELECT @txtPtr = 0

WHILE @txtPtr IS NOT NULL
BEGIN
 SELECT @txtPtr = null
 SELECT  TOP 1 @i = i, @txtPtr = textptr(t)
 FROM test
 WHERE t LIKE '%' + @old + '%'
 
 IF @txtPtr IS NOT NULL
 BEGIN
  SELECT @offset = 1
  WHILE @offset <> 0
  BEGIN
   SELECT @offset = patindex('%' + @old + '%', t)
   FROM test
   WHERE i = @i
   --SELECT offset = @offset
   
   IF @offset > 0
   BEGIN
    SELECT @offset = @offset-1
    UPDATETEXT test.t @txtPtr @offset @dellen @new
   END
  END -- end while
 END-- End if
END-- end while
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now