Need help on a proc (updating a XML in text format

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help on a proc (updating a XML in text format

Author

Topic

deepugun
Starting Member

11 Posts

Posted - 2013-06-06 : 17:58:25

Hello Everyone,
I am new to sql server, I am trying to come up with a sql block through which i can update the XML stored in a table as a text. In order to do that, i am converting the text to XML and also getting the values from other tables dynamically and then use those values o update the XML and then convert the XML back to text and then update the base table with the modified text (XML).While i am doing this, i have encountered this error i.e.
An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.

and also the procedure is very slow.Please go throw my code and let me know if there is any other way to accomplish which does not throw the memory error and also is faster in execution .
Please find the code below.

DECLARE @uniqueId int
DECLARE @claimnum claimnum
DECLARE @TEMP TABLE (uniqueId int)
DECLARE @XML_AFTERCAST XML
DECLARE @NAME VARCHAR(MAX)
DECLARE @NAME1 VARCHAR(MAX)
-- Insert into the temporary table a list of the records to be updated
INSERT INTO @TEMP (uniqueId)
SELECT EDI_Assessment_ID FROM ED_TEST
-- Start looping through the records
WHILE EXISTS (SELECT * FROM @TEMP)
BEGIN
-- Grab the first record out
SELECT Top 1 @uniqueId = uniqueId FROM @TEMP
Select @XML_AFTERCAST = CAST(XML_Text AS XML),@claimnum = Claim_Num from ED_TEST where ED_ID = @uniqueId
Select @NAME = cl.First_Name + Case When Coalesce(cl.Middle_Name, '') = '' Then ' ' Else ' ' + cl.Middle_Name + '.' End ,
@NAME1 = cl.Last_Name
From
Customer_Role cr Join Customer cl On
cr.Client_Id = cl.Client_Id
Where
cr.Claim_Num =(SELECT CLAIM_NUM FROM ED_TEST WHERE ED_ID=@uniqueId)
And
cr.test_Code = '1'
Select @Name
Select @Name1
-- Perform some Replace on the Xml
Select @uniqueId
Select @NAME
Select @XML_AFTERCAST
SET
@XML_AFTERCAST.modify('replace value of (/Assessment/Insured/First_Name/text())[1] with sql:variable("@NAME")')
SET
@XML_AFTERCAST.modify('replace value of (/Assessment/Insured/Last_Name/text())[1] with sql:variable("@NAME1")')

UPDATE
dbo.ED_test
SET
XML_TEXT = CAST(CAST(@XML_AFTERCAST AS VARCHAR(MAX)) AS TEXT)
WHERE
ED_ID= @uniqueId
select * from ED_test where ED_id = @uniqueId
-- Drop the record so we can move onto the next one
DELETE FROM @TEMP WHERE uniqueId = @uniqueId
END

Thanks for the help!!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-07 : 01:11:41

the way its written currently you're doing one row update at a time.
Why not use a SET based approach which will just have a single update doing entire XML document replacing together.
it would be something like


UPDATE t
SET XML_TEXT = XML_TEXT.modify('replace value of (/Assessment/Insured/First_Name/text())[1] with sql:column("NAME")'),
XML_TEXT = XML_TEXT.modify('replace value of (/Assessment/Insured/Last_Name/text())[1] with sql:column("NAME1")')
FROM (SELECT CAST(XML_TEXT AS xml) AS XML_TEXT,CLAIM_NUM
      FROM dbo.ED_test e
     )t
INNER JOIN (Select NAME = cl.First_Name + Case When Coalesce(cl.Middle_Name, '') = '' Then ' ' Else ' ' + cl.Middle_Name + '.' End ,
NAME1 = cl.Last_Name
From
Customer_Role cr Join Customer cl On
cr.Client_Id = cl.Client_Id
Where cr.test_Code = '1'
)u
ON u.Claim_Num = t.CLAIM_NUM

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

deepugun
Starting Member

11 Posts

Posted - 2013-06-07 : 16:22:25

Hello Visakh,
Thanks for the help. I am trying to modify the query based on you suggestions.

i tried to just load the data into a test table and it throws the following error.

select CAST(XML_TEXT AS XML) workXML into test
from dbo.ED_Test

Msg 9411, Level 16, State 1, Line 2
XML parsing: line 54, character 29, semicolon expected

But when i just ran the select query i.e.

select CAST(XML_TEXT AS XML) workingXML
from dbo.ED_Test

It works fine .If possible please let me know what might be the reason.
thanks..

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-07 : 18:18:58

quote:
Originally posted by deepugun

Hello Visakh,
Thanks for the help. I am trying to modify the query based on you suggestions.

i tried to just load the data into a test table and it throws the following error.

select CAST(XML_TEXT AS XML) workXML into test
from dbo.ED_Test

Msg 9411, Level 16, State 1, Line 2
XML parsing: line 54, character 29, semicolon expected

But when i just ran the select query i.e.

select CAST(XML_TEXT AS XML) workingXML
from dbo.ED_Test

It works fine .If possible please let me know what might be the reason.
thanks..

That sounds like the problem is somewhere else in your code - not in the two statements that you have shown. Are you showing the entire query, or is there more code before and after the "SELECT .. INTO"?

deepugun
Starting Member

11 Posts

Posted - 2013-06-07 : 20:43:28

Hello James,
I have identified that the problem is in this statement
select CAST(XML_TEXT AS XML) workXML into test
from dbo.ED_Test
When i run this query to create a table called test , the below error is being thrown

Msg 9411, Level 16, State 1, Line 2
XML parsing: line 54, character 29, semicolon expected

but if i just ran the select query

select CAST(XML_TEXT AS XML) workingXML
from dbo.ED_Test
it is working fine.
if possible let me know what might be the issue

quote:
Originally posted by James K

quote:
Originally posted by deepugun

Hello Visakh,
Thanks for the help. I am trying to modify the query based on you suggestions.

i tried to just load the data into a test table and it throws the following error.

select CAST(XML_TEXT AS XML) workXML into test
from dbo.ED_Test

Msg 9411, Level 16, State 1, Line 2
XML parsing: line 54, character 29, semicolon expected

But when i just ran the select query i.e.

select CAST(XML_TEXT AS XML) workingXML
from dbo.ED_Test

It works fine .If possible please let me know what might be the reason.
thanks..
That sounds like the problem is somewhere else in your code - not in the two statements that you have shown. Are you showing the entire query, or is there more code before and after the "SELECT .. INTO"?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-08 : 03:12:04

can you post how XML looks?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

deepugun
Starting Member

11 Posts

Posted - 2013-06-08 : 11:42:20

Hello,
Unfortunately i cannot really post the XML ..So please let me know the debugging steps and i will prove the feedback.
Thanks

quote:
Originally posted by visakh16

can you post how XML looks?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-08 : 13:51:21

cant understand what issue without seeing XML as it seems like XML parse error

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-08 : 19:14:08

quote:
Originally posted by deepugun

Hello,
Unfortunately i cannot really post the XML ..So please let me know the debugging steps and i will prove the feedback.
Thanks

quote:
Originally posted by visakh16

can you post how XML looks?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

There are two possibilities:

1. It is in the query. If your entire query is just the select or select into (i.e., you are not just posting part of your query), then the problem is not in the query.

2. It is in the XML. Since you are unable to post the XML, you would have to debug it yourself. I am puzzled because you are just moving data from one XML column to another XML column and there should be no errors because it is already stored as valid XML.

In any case, one way is to copy the offending XML and put it into a variable and simply do a select. For example, the following will give you the same error:

declare @x xml = 
'<root>
	<node>abcd</node>
	<node>abcd&efgh</node>
</root>';
select @x;

That requires a semi-column after the & like shown below, which will run without error:

declare @x xml = 
'<root>
	<node>abcd</node>
	<node>abcd&efgh</node>
</root>';
select @x;

deepugun
Starting Member

11 Posts

Posted - 2013-06-11 : 11:29:33

Thanks(both visakh and james) for the help.I used a cursor and found that was a XML data issue for one of the record in the table on which i am working on.Now i have eliminated that record from the update where clause and trying to run the query. I have ran the cursor based update and it took around 8 hours to finish(i.e. it worked on approximately on 550,000 records).As i am running a set based update as suggested in a earlier post, I hope that it is faster.I will keep u guys posted with the results.

Thanks

quote:
Originally posted by James K

quote:
Originally posted by deepugun

Hello,
Unfortunately i cannot really post the XML ..So please let me know the debugging steps and i will prove the feedback.
Thanks

quote:
Originally posted by visakh16

can you post how XML looks?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

There are two possibilities:

1. It is in the query. If your entire query is just the select or select into (i.e., you are not just posting part of your query), then the problem is not in the query.

2. It is in the XML. Since you are unable to post the XML, you would have to debug it yourself. I am puzzled because you are just moving data from one XML column to another XML column and there should be no errors because it is already stored as valid XML.

In any case, one way is to copy the offending XML and put it into a variable and simply do a select. For example, the following will give you the same error:
declare @x xml =
'<root>
<node>abcd</node>
<node>abcd&efgh</node>
</root>';
select @x;
That requires a semi-column after the & like shown below, which will run without error:
declare @x xml =
'<root>
<node>abcd</node>
<node>abcd&efgh</node>
</root>';
select @x;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources