SQL Server 2000: Trouble with writing xml output

Question

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?

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
– will, Commented Nov 16, 2010 at 17:14
What's the data look like, and what do you need as a result? — OMG Ponies
– OMG Ponies, Commented Nov 16, 2010 at 17:17
It is just an integer , i want the output to say <loc>9</loc> — will
– will, Commented Nov 16, 2010 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
– marc_s, Commented Nov 16, 2010 at 17:42
Just in case: this is a good article on what is supported in terms of XML in SQL Server 2000: msdn.microsoft.com/en-us/magazine/cc163782.aspx — marc_s
– marc_s, Commented Nov 16, 2010 at 17:45

Luke · Accepted Answer · 2010-11-16 17:19:02Z

2

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

answered Nov 16, 2010 at 17:19

Luke

1761 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ScottE · Accepted Answer · 2010-11-16 17:28:01Z

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

answered Nov 16, 2010 at 17:28

ScottE

21.6k19 gold badges97 silver badges131 bronze badges

Comments

DJ. · Accepted Answer · 2010-11-16 17:21:26Z

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

answered Nov 16, 2010 at 17:21

DJ.

16.3k3 gold badges45 silver badges46 bronze badges

1 Comment

will Over a year ago

right, but i get this error:The FOR XML clause is not allowed in a ASSIGNMENT statement.

Collectives™ on Stack Overflow

SQL Server 2000: Trouble with writing xml output

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related