Convert the sql query result into xml datatype

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

General SQL Server Forums

New to SQL Server Programming

Convert the sql query result into xml datatype

Author

Topic

ocs2me
Starting Member

5 Posts

Posted - 2012-06-19 : 05:48:31

I wish to convert the sql query result into xml datatype.

Below is my select syntax. I try to add the xml tag to enable it become xml datatype.

sqltest = "select RCATT as ('<Food>' || RCATT || '</Food>') from table"

It seems that my select statment facing some error. Wish to get help for it.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-19 : 06:57:41

quote:
Originally posted by ocs2me

I wish to convert the sql query result into xml datatype.

Below is my select syntax. I try to add the xml tag to enable it become xml datatype.

sqltest = "select RCATT as ('<Food>' || RCATT || '</Food>') from table"

It seems that my select statment facing some error. Wish to get help for it.

Can you post and example of the XML you are expecting to get? May be this?

SELECT
	RCATT AS [Food]
FROM
	table
FOR XML PATH('');

ocs2me
Starting Member

5 Posts

Posted - 2012-06-19 : 07:01:59

I have a table Submission Record which contain a field with mix with text string and xml data type.

Table name: Submission Record
Field name: RCA
Jason
Tomato
<Record>AA</Record>
Fish
Brother
<Record>BB</Record>
<Record>CC</Record>
Tom is a girl

Its mixing with text and xml data.

I wish to convert all into xml data as per request from management.

my select statement is like below...but i think it may contain syntax error. Wish to get help from here.

sqltestagain = "select '<DATA>' || RCA || '</DATA>' from Submission Record".

Expect below will display

<DATA>Jason</DATA>
<DATA>Tomato</DATA>
<DATA><Record>AA</Record></DATA>

'
'
'
'
'

or any other solution for it ??

I've tried FOR XML....DBMS_XML......but none of it worked.....

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-19 : 09:26:24

The following may work. However, if you have data in the table that cannot be cast to XML, it will fail. I don't have a solution for what to do if that indeed is the case.

SELECT
	XRCA AS [DATA]
FROM
	( 
		SELECT CAST(RCA AS XML) XRCA FROM table
	) a
FOR XML PATH('');

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-19 : 09:29:15

BTW, are you using Microsoft SQL Server? If you are using another RDBMS, what I posted is totally useless. I am asking because you were using || operators, which are not part of Microsoft's version of SQL language.

This forum is for MS SQL Server. If you are on another RDBMS, you would get better and faster answers if you post to a forum such as dbforums.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources