FOR XML for 2000 and 2005

Question

FOR XML for 2000 and 2005

James Stevens-465623

Old Hand

Points: 314
More actions
January 2, 2008 at 7:24 am

#93021

Hi,
Sorry for the length of this post, but I hope someone can help me here; I'm close to telling all our clients to upgrade to 2005 just to make this easier on me!
I have to create xml documents from our database which can be installed on 2000 and 2005. Thing is, the xml structure has an unnecessary level in my opinion but it's out of my control to have it removed. The structure is:
&ltroot level&gt
&ltlevel1&gt
&ltlevel2&gt
&ltdata elements&gt
...
&lt/level2&gt
&lt/level1&gt
&ltlevel1&gt
&ltlevel2&gt
&ltdata elements&gt
...
&lt/level2&gt
&lt/level1&gt
...
&lt/root level&gt
I have written some SQL that gets the right output in 2005, but everything I try to make it work in 2000 is failing. The SQL I have tried in 2005 is:
SELECT col1 AS 'level2/element1',
col2 AS 'level2/element2',
col3 AS 'level2/element3'
FROM view
FOR XML PATH('level1'), TYPE, ELEMENTS, ROOT('rootlevel')
This fails on 2000 because the PATH method isn't valid.
Next I tried (as per Jacob Sebastian's tutorials on xml):
SELECT CAST(&lt'level1'&gt +
(SELECT level2.col1 AS 'element1',
level2.col2 AS 'element2',
level2.col3 AS 'element3'
FROM view level2
FOR XML AUTO, ELEMENTS) + '&lt/level1&gt' AS XML)
This fails on 2000 because the XML data type isn't valid. Or so I thought, because when I tried:
SELECT CAST(&lt'level1'&gt +
(SELECT level2.col1 AS 'element1',
level2.col2 AS 'element2',
level2.col3 AS 'element3'
FROM view level2
FOR XML AUTO, ELEMENTS) + '&lt/level1&gt' AS varchar(4000))
it still gives me the same error of 'Incorrect syntax near 'XML' on the last line.
I also investigated the use of EXPLICIT but don't think this will work as I have an extra level (level1) and also I can't seem to get the elements to be output as above. EXPLICIT only seems to give the elements with the values as attributes.
Anyone have any ideas? If so, they'd be gratefully received because I'm up against it on this one.
Thanks,
James

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

DanKennedy Hall of Fame Points: 3736 More actions · Answer 1

James,

I suspect you're right that it requires the use of XML EXPLICIT for SQL 2000. Can you post a sample table containing the data that will be used to source the output. I'll see if I can knock together an explicit query to produce what you need.

Cheers

Update: I've had a play and have come up with this. The output looks like what you want but I've guessed at the table schema and data. Hope it helps.

Dan

DECLARE @t TABLE

(

col1 varchar(10),

col2 varchar(10),

col3 varchar(10)

)

INSERT INTO @t VALUES ('Value1', 'Value2', 'Value3')

INSERT INTO @t VALUES ('Value11', 'Value2', 'Value3')

INSERT INTO @t VALUES ('Value111', 'Value22', 'Value33')

INSERT INTO @t VALUES ('Value1111', 'Value2', 'Value33')

INSERT INTO @t VALUES ('Value11111', 'Value2', 'Value33')

SELECT1 AS Tag,

NULL AS Parent,

NULL AS [rootlevel!1!],

NULL AS [level1!2!],

NULL AS [level1!2!OrderEntry!hide],

NULL AS [level2!3!],

NULL AS [level2!3!OrderEntry!hide],

NULL AS [level2!3!element1!element],

NULL AS [level2!3!element2!element],

NULL AS [level2!3!element3!element]

UNION

SELECT2, 1, NULL,

NULL, col1,

NULL, NULL,

NULL, NULL, NULL

FROM @t

UNION

SELECT3, 2, NULL,

NULL, col1,

NULL, col2,

col1, col2, col3

FROM @t

ORDER BY

[level1!2!OrderEntry!hide],

[level2!3!OrderEntry!hide]

FOR XML EXPLICIT

Dan
www.firstcs.co.uk

James Stevens-465623 Old Hand Points: 314 More actions · Answer 2

Thanks Dan for your suggestion. I had made some progress yesterday using EXPLICIT, but you helped me with some final issues I was having. Only thing is I now realize that I have another group at level2 and I'm struggling to get it in the order.

Thanks again!

James

DanKennedy Hall of Fame Points: 3736 More actions · Answer 3

You can expand the example to add another level grouping in the same way the level2 grouping is created. If you're still struggling by all means post the table schema with some sample data and the required output xml structure and I'll try to get a look at it later.

Dan
www.firstcs.co.uk

James Stevens-465623 Old Hand Points: 314 More actions · Answer 4

OK Dan, I was thinking I was close, but now I've got a headache!

I'm trying to move a group of elements up the file a little and can't come up with it. See the problem.txt for description, xml samples and sql, and problem.xls for the records used in the for xml sql.

I'm pretty much stumped, I can't seem to get rid of the error I state at the bottom, regardless of what order of what fields I put in the ORDER BY clause...:ermm:

Any ideas????

Thanks,

James