Fetching XML data from Text field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Fetching XML data from Text field

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-09-14 : 06:44:29

Arvind Saxena writes "I would like to know whether can we transform/query XML data to normal form in MS SQL.

I have table called XMLin :
CDR_ID numeric
Mnumber varchar
SOURCE varchar
SVC_ID varchar
SVC_ACCESS_TIME varchar
TXN_ID varchar
TXN_STATE varchar
INFO text

Example data is:

236361 NULL Gateway NULL NULL NULL NULL <?xml version="1.0" encoding="ISO8859-1" ?> <ACCOUNTINGCDR> <original_jar_size>0</original_jar_size> <context-chargingdata>null</context-chargingdata> <modified_jar_size>0</modified_jar_size> <fulfillmentThroughJmss>false</fulfillmentThroughJmss> ....etc

So last field is INFO(datatype is text(16) have all the XML data.

Now I would like to normalize this data to another table, like all the XML tag will become a field in new table and its data get filld.

or I can query this data like

Select * from XMLin where info(<context-chargingdata> =something)

One more query I have, While doing query like select * from xmlin SQL query analyzer will show 255 Character of info field. How DO I display > 255 character ?

let me know if you need further clarification"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources