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 want to update all xmls how can this be done in an most efficient way?

Can you show some real XML, I can't understand what your current data looks like?
And just to confirm, you are using SQL Server 2000?

--

Joe (Microsoft MVP - XML)

That was a real xml only... this is what I have in ntext column. Included < > tags now. I am using SQL 2000..if I upgrade it to 2005 will there be improvement in the performance??


<?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</TextAttributeValue>
</StatusOfNegotiation>
<CounterpartyName AttributeID="31">
<TextAttributeValue> ABC CO Ltd </TextAttributeValue>
</CounterpartyName>
</CommonAttributes>

Well SQL Server 2005 has an XML type with methods that would probably make things easier.
Are you sure you have this: xmlns="http://www.w3.org/2001/XMLSchema.xsd"?
It means all the elements are in that namespace, it's very odd but doesn't really affect the issue.

In 2000 you will probably have to use sp_xml_preparedocument, shred the data into a temporary table, manipulate and update original data. But that would need a cursor and on thousands or rows would perform terribly.

--

Joe (Microsoft MVP - XML)

I got the solution...the only problem i m facing now is casting XML type to ntext ?? how do i achieve this?

UPDATE tblename SET ntextcolname=CAST (replace(CAST(@x as nvarchar(xml)), 'utf-16', 'utf-8') AS ntext) where p_id = 1312312;


@x is xml type I used xml queries to modify the node values. Before that my xmls are having encoding as utf-8 so converted to utf-16 while casting it into xml type. then used modify query.

set @x.modify('declare namespace s="http://www.w3.org/2001/XMLSchema.xsd";
replace value of (s:CommonAttributes/s:CounterpartyName/s:TextAttributeValue[1]/text())[1] with "XYZ"' );

UPDATE tblename SET ntextcolname=CAST (replace(CAST(@x as nvarchar(xml)), 'utf-16', 'utf-8') AS ntext) where p_id = 1312312;


but how do i get it into original format (ntext with encoding utf-8) before updating it to database.
?


Thanks

ok casting problem is solved. The other problem is

when I run the following
DECLARE @x xml;
SELECT @x=CAST (replace(CAST(ntextcolname as nvarchar(max)), 'utf-8', 'utf-16') AS xml) FROM tblename WHERE p_id = 901100747;
SELECT @x;

x =
doesnt contain the header

<?xml version="1.0" encoding="utf-16"?>

How do I get that??

Well you don't need it really as utf-16 is one of the defaults but if you want you'll have to add it in by hand as it's lost when casting NTEXT to XML as an XML data type doesn't need it.

--

Joe (Microsoft MVP - XML)

ok. That problem is solved.

One more thing can i use variable in replace with function. e.g

replace value of (s:CommonAttributes/s:CounterpartyName/s:TextAttributeValue[1]/text())[1] with "XYZ"'+@id+'""')


something like this??? it isnt working though. id is variable here.

You maybe able to use sql:variable(), alternatively you need to use dynamic SQL, updating in XQuery was not finalised when SQL Server 2005 came out so there's no standard way.

--

Joe (Microsoft MVP - XML)

Can i convert my entire ntext column to xml type.... but all my xmls are stored with encoding utf-8.

if i want to create new table with column of xml data type n copy my ntext columnin it..how do i do that...is it possible??