July 23, 2008 at 9:01 am
Hi,
I have a XML document stored in NTEXT column.
One of my xml is..(this is stored in ntext column)
Please assume these tags....
?xml version="1.0" encoding="utf-8"?
CommonAttributes xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" ContractID="6243140" xmlns="http://www.w3.org/2001/XMLSchema.xsd"
ContractDate AttributeID="1" ValueID="07/01/2004 00:00:00"
DateAttributeValue>2004-01-07/DateAttributeValue
/ContractDate
ContractType AttributeID="2" ValueID="10"
TextAttributeValue>AFTI/TextAttributeValue
/ContractType
StatusOfContract AttributeID="8" ValueID="20"
TextAttributeValue>Done/TextAttributeValue
/StatusOfContract
StatusOfNegotiation AttributeID="590" ValueID="3">
TextAttributeValue>Executed - Fully Complete
/StatusOfNegotiation>
CounterpartyName AttributeID="31"
TextAttributeValue>cpty/TextAttributeValue
/CounterpartyName
/CommonAttributes
I want to replace CounterpartyName Textattribute value with another value.
How can I achive this??
I have around 3 lakhs of data in table..(3lakhs xmls). I have the primary key...i wan tto update all xmls how can this be done in an most efficient way?
July 23, 2008 at 1:34 pm
trk225, can you tell me *exactly* which characters you are trying to replace? XML is greek to me, so I don't know what you are trying to do.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
July 23, 2008 at 4:36 pm
Ok, without having any more input, I got bored at work, and saw in this a good opportunity to try my limited skills. As always, the caveats are many, but here goes. You should be able to just copy and past and execute.
--Create a test table
IF OBJECT_ID('TempDB..#xml','u') IS NOT NULL
DROP TABLE #xml
CREATE TABLE #xml
(
id int identity(1,1),
string ntext
)
--Populate with test data meant to serve as the xml record
--First a copy and past of your post, and I don't know what should be included
--Then just a sample that is easier to read
INSERT INTO #xml
SELECT '?xml version="1.0" encoding="utf-8"?
CommonAttributes xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" ContractID="6243140" xmlns="http://www.w3.org/2001/XMLSchema.xsd"
ContractDate AttributeID="1" ValueID="07/01/2004 00:00:00"
DateAttributeValue>2004-01-07/DateAttributeValue
/ContractDate
ContractType AttributeID="2" ValueID="10"
TextAttributeValue>AFTI/TextAttributeValue
/ContractType
StatusOfContract AttributeID="8" ValueID="20"
TextAttributeValue>Done/TextAttributeValue
/StatusOfContract
StatusOfNegotiation AttributeID="590" ValueID="3">
TextAttributeValue>Executed - Fully Complete
/StatusOfNegotiation>
CounterpartyName AttributeID="31"
TextAttributeValue>cpty/TextAttributeValue
/CounterpartyName
/CommonAttributes' UNION ALL
SELECT 'Just some characters
CounterpartyName AttributeID="31"
Just the rest of it'
--Declare some local variables
DECLARE @start VARCHAR(100) --Simply the line to be changed up to the value to be changed
DECLARE @newval VARCHAR(100)--New value to be updated to xml record
SELECT @start = 'CounterpartyName AttributeID="' --Change this for any line of the xml record
SELECT @newval = '32'
--First, see what you are going to change
SELECT
OldString = string,
OldVal = SUBSTRING(string,CHARINDEX(@start,string) + LEN(@start),
(CHARINDEX('"',string,CHARINDEX(@start,string) + LEN(@start))-1)-(CHARINDEX(@start,string) + LEN(@start)-1)),
FirstPart = SUBSTRING(string,1,CHARINDEX(@start,string) + LEN(@start)-1),
LastPart = SUBSTRING(string,CHARINDEX('"',string,CHARINDEX(@start,string) + LEN(@start)),1000),
--For the last part, I could not figure out a way to use a variable instead of 1000 with ntext,
--so I just used 1000 arbitrarily
NewString = SUBSTRING(string,1,CHARINDEX(@start,string) + LEN(@start)-1)+ @newval +
SUBSTRING(string,CHARINDEX('"',string,CHARINDEX(@start,string) + LEN(@start)),100)
FROM #xml
--Then change it
UPDATE #xml
SET string = SUBSTRING(string,1,CHARINDEX(@start,string) + LEN(@start)-1)+ @newval +
SUBSTRING(string,CHARINDEX('"',string,CHARINDEX(@start,string) + LEN(@start)),1000)
--Then see it updated
SELECT
string
FROM #xml
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy