June 27, 2011 at 5:54 am
Dear All,
i have xml datatype column in a table. i can able to insert this "<tst>1>0</tst>" data in xml field.But it was changed into
below mentioned form. can i able to read this xml as inserted value like <tst>1>0</tst>?
<tst><id>1>0</id></tst>
June 27, 2011 at 1:40 pm
I cannot reproduce this behavior. Can you please post some sample code to show that SQL Server changed your data?
CREATE TABLE dbo.xml_test (xml_data XML) ;
INSERT INTO dbo.xml_test
(xml_data)
VALUES ('<tst>1>0</tst>') ;
SELECT *
FROM dbo.xml_test ;
Results:
<tst>1>0</tst>
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 27, 2011 at 11:42 pm
Dear opc.three,
sorry, my result same as your result. I need with out change as '<tst>1>0</tst>'
June 28, 2011 at 6:57 am
k.thanigaivel (6/27/2011)
Dear opc.three,sorry, my result same as your result. I need with out change as '<tst>1>0</tst>'
Sorry, you cannot, the > is a reserved character in XML for use as part of the syntax itself...therefore when it appears in a text value it is escaped to keep other XML parsers happy. When you retrieve the XML and pass it on downstream a proper XML parser will know what's going on and should display it properly (note how this web page showed it as '<tst>1>0</tst>' and not as '<tst>1>0</tst>'). What is your underlying concern?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 28, 2011 at 7:24 am
thank for your replay.i am looking for inbuild function or any other way to view my data with out this type of conversion.because our development team is desire to retrive as inserted.
June 28, 2011 at 7:34 am
They can either use any normal XML-reader (SSMS works for that), or they can store the data as something other than XML. Those are really the options available.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 28, 2011 at 7:35 am
k.thanigaivel (6/28/2011)
thank for your replay.i am looking for inbuild function or any other way to view my data with out this type of conversion.because our development team is desire to retrive as inserted.
I do not know of any functions that will do that for you. First you would need to convert the data from the XML type to the NVARCHAR type, then you would need to do some string replacements.
Consider this input:
SELECT CAST( N'<tst>1>0 &amp; 7>5</tst>' AS XML) AS xml_data
Returns:
<tst>1>0 &amp; 7>5</tst>
How would you know that the first greater than sign was explicit and the second one was escaped in the original input? You couldn't know that.
EDIT: uggh, the web page ate some characters, I edited to correct the example
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 29, 2011 at 12:11 am
thks all, i suggested NVACHAR for xml type values is it ok?
June 29, 2011 at 12:20 am
k.thanigaivel (6/29/2011)
thks all, i suggested NVACHAR for xml type values is it ok?
It depends on what you need to do with the data while it's in the database. Do you need to query the XML data using T-SQL? If so then it's best to store the data in an XML-typed column. There is also a difference in required storage space for each data type for equivalent data, the XML data type is much more efficient.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply