|
HOW TO: Get the XML text of a FOR XML query within SQL. (35613 Requests)
Is there a way to get the XML text of a FOR XML query?
Answer provided by Erland Sommarskog.
Check Erland's website for more great SQL Server information.
In SQL2000 you can do this:
create table xml (x ntext)
go
insert xml
SELECT * FROM OPENQUERY(MSDALOCAL,
'SELECT * FROM sysobjects FOR XML AUTO')
go
select * from xml
go
drop table xml
MSDALOCAL is a linked server, which can be a loopback to your own server.
But, this is important, it has to be set up with MSDASQL - OLE DB over
ODBC. If you use SQLEOLDDB, which is the default, then you get binary
data instead.
If you want the data in a variable, beware that you cannot declare ntext
variables in T-SQL, you would have to use nvarchar(4000) which may cause
truncation.
In SQL2005 there is a xml datatype, all these sort of things are simpler.
Feedback
# re: HOW TO: Get the XML text of a FOR XML query within SQL.
12/2/2004 1:38 AM
Colin Sheppard
How could this be adapted for a single ntext file that contains an entire XML file and have it written out to a (text) XML file?
# re: HOW TO: Get the XML text of a FOR XML query within SQL.
12/2/2004 1:39 AM
Colin Sheppard
How could this be adapted for a single ntext field that contains an entire XML file and have it written out to a (text) XML file?
# MSDE and XML how to???
12/8/2004 6:23 AM
Ligeiro
Hallo!
I need to read data from MSDE database as XML document and then trasmit it to another database.
I alredy have the XML XSD where data can be send to. Is any budy familiar with this problem? And how did you solve it.
# XML and MSDE How to??.
12/14/2004 5:04 AM
Ligeiro
XML and MSDE
--------------------------------------------------------------------------------
Msde and XML (How to??)
--------------------------------------------------------------------------------
Hallo! every one,
My goal is to build up an application that will run on a Msde server database . Wilth this application i want to be able to retrive data from my database as XML data and transmit it to another database which wil store the xml documents.
Can any one tell me if this is possible. Am not aware of the XML built in possibility of MSDE. And not able to buying a full version of MS sql server with xml possibilty.
So any suggestin, artikels or exampels are wormly welcome
# re: HOW TO: Get the XML text of a FOR XML query within SQL.
9/27/2005 12:52 PM
Jonathan
Bad example
Operand type clash: image is incompatible with ntext
# re: HOW TO: Get the XML text of a FOR XML query within SQL.
10/26/2005 1:19 PM
JW
I got same as Jonathan
Operand type clash: image is incompatible with ntext
# re: HOW TO: Get the XML text of a FOR XML query within SQL.
5/3/2006 4:17 AM
kiru
fine
# re: HOW TO: Get the XML text of a FOR XML query within SQL.
5/30/2006 12:00 PM
KenA
Through a linked server, even if it´s loopedback ... is it the onlw way out? Plus, using oledb?
# how to get xml data into SQL server/express using options in SQL server/express
12/28/2006 1:38 AM
DP
Send answer for this
# XML with using SQL
7/11/2007 5:43 AM
karthik
HOW TO: Get the XML text of a FOR XML query within SQL. and How to return on XML to Visual basic
# XML with using SQL
7/11/2007 5:43 AM
karthik
HOW TO: Get the XML text of a FOR XML query within SQL. and How to return on XML to Visual basic
# re: HOW TO: Get the XML text of a FOR XML query within SQL.
7/17/2007 7:04 AM
Zx7R
Hi.
how can i get InnerXml under certain parent and save it including tags as a string?
# re: HOW TO: Get the XML text of a FOR XML query within SQL.
7/25/2007 6:03 AM
KGS
Right, so I have to convert this sql 2005 code back to sql 2000 :
declare @string varchar(8000)
set @string = (select * from Clients for xml auto)
very basic, but I just can't get it figured out. Sql 2000 won't allow me to assign the select statement to a varchar variable. How do I do it?
Thanks guys.
# re: HOW TO: Get the XML text of a FOR XML query within SQL.
7/25/2007 6:15 AM
KGS
Right, so I have to convert this sql 2005 code back to sql 2000 :
declare @string varchar(8000)
set @string = (select * from Clients for xml auto)
very basic, but I just can't get it figured out. Sql 2000 won't allow me to assign the select statement to a varchar variable. How do I do it?
Thanks guys.
ytbqjihep fienqtj mqgicf nocukjtz yekrp jhdlxbvk gwtb
# re: HOW TO: Get the XML text of a FOR XML query within SQL.
11/27/2007 9:32 AM
wasjgcgqak
Hello! Good Site! Thanks you! lrakwnhjjc
# re: HOW TO: Get the XML text of a FOR XML query within SQL.
4/29/2008 12:27 AM
ravi,srirangam
how can i get InnerXml under certain parent and save it including tags as a string
# re: HOW TO: Get the XML text of a FOR XML query within SQL.
7/2/2008 3:54 AM
H_graen
I got this error
Could not find server 'MSDALOCAL' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
# re: HOW TO: Get the XML text of a FOR XML query within SQL.
10/29/2008 10:09 AM
bzyk333
Full resolution:
use [master]
create table textxml (aaa text)
go
--sp_linkedservers --display linkedserver
Declare @provider_string varchar(50);
Set @provider_string = 'DRIVER={SQL Server};SERVER=' + @@servername;
--you must use 'MSDASQL' if you want text
EXEC sp_addlinkedserver @server='LOCALSERVER',
@srvproduct='ODBC', @provider='MSDASQL',
@provstr=@provider_string
go
insert textxml
SELECT * FROM OPENQUERY(LOCALSERVER, 'SELECT srvname FROM sysservers for xml auto')
go
select aaa from textxml
go
drop table textxml
go
sp_dropserver 'LOCALSERVER'
# re: HOW TO: Get the XML text of a FOR XML query within SQL.
12/4/2008 4:47 AM
amjad
hin main kuro mushkil gal aahe charya bena
|