SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Need help on a proc (updating a XML in text format
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

deepugun
Starting Member

11 Posts

Posted - 06/06/2013 :  17:58:25  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 06/07/2013 :  01:11:41  Show Profile  Reply with Quote
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
Go to Top of Page

deepugun
Starting Member

11 Posts

Posted - 06/07/2013 :  16:22:25  Show Profile  Reply with Quote
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..
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3660 Posts

Posted - 06/07/2013 :  18:18:58  Show Profile  Reply with Quote
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"?
Go to Top of Page

deepugun
Starting Member

11 Posts

Posted - 06/07/2013 :  20:43:28  Show Profile  Reply with Quote
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"?

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/08/2013 :  03:12:04  Show Profile  Reply with Quote
can you post how XML looks?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

deepugun
Starting Member

11 Posts

Posted - 06/08/2013 :  11:42:20  Show Profile  Reply with Quote
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


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/08/2013 :  13:51:21  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3660 Posts

Posted - 06/08/2013 :  19:14:08  Show Profile  Reply with Quote
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;

Edited by - James K on 06/08/2013 19:14:46
Go to Top of Page

deepugun
Starting Member

11 Posts

Posted - 06/11/2013 :  11:29:33  Show Profile  Reply with Quote
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;


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000