• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 698
  • Last Modified:

Update text column converting to xml and updating via XPATH!

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
macros14
Asked:
macros14
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
u can't update a text field usinf UPDATE command, u need to use WRITETEXT and UPDATETEXT
0
 
macros14Author Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now