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