2

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?

p.campbell
  • 98,673
  • 67
  • 256
  • 322
will
  • 35
  • 1
  • 4
  • also when i add @XMLOutput as a variable and set its data type to xml it says it is not a valid data type – will Nov 16 '10 at 17:14
  • 1
    What's the data look like, and what do you need as a result? – OMG Ponies Nov 16 '10 at 17:17
  • It is just an integer , i want the output to say 9 – will Nov 16 '10 at 17:19
  • SQL Server 2000 **doesn't** have a XML data type... You need to upgrade to at least SQL Server 2005 – marc_s Nov 16 '10 at 17:42
  • Just in case: this is a good article on what is supported in terms of XML in SQL Server 2000: http://msdn.microsoft.com/en-us/magazine/cc163782.aspx – marc_s Nov 16 '10 at 17:45

3 Answers3

2

XML datatype support wasn't included in SQL Server until SQL Server 2005.

Luke
  • 176
  • 1
  • 4
0

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
DJ.
  • 16,045
  • 3
  • 42
  • 46
  • right, but i get this error:The FOR XML clause is not allowed in a ASSIGNMENT statement. – will Nov 16 '10 at 17:22
0

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
ScottE
  • 21,530
  • 18
  • 94
  • 131