I need to set a variable @XMLOutput to the value of the subquery in XMLformat.
The subquery works fine on its own, but when the whole query is run i get the error:
Incorrect syntax near XML.
SELECT @XMLOutput = (SELECT loc
FROM ghhsitemap url
FOR XML AUTO, Elements)
A second problem: when specifying @XMLOutput as a variable and set its data type to xml it says it is not a valid data type.
How can this be resolved?
XML datatype support wasn't included in SQL Server until SQL Server 2005.
This isn't supported in sql 2000. And don't bother trying to return it inside a correlated subquery either - that also will not work in sql 2000. Fun eh?
In you situation, since your xml is so straightforward I'd just build the xml as a string and return it.
DECLARE @XmlString varchar(500)
SELECT @XmlString = '<loc>' + Cast(loc as varchar(8)) + '</loc>' FROM ghhsitemap
Just declare @XMLOutput as a varchar()
Your syntax for setting values is wrong - it should be like this:
SELECT @XMLOutput = loc FROM ghhsitemap url FOR XML AUTO, Elements
