September 24, 2008 at 10:23 am
Hi all,
Hope you can help. This is not an urgent query, but it's bugging me as to why I can't get this to work :angry:
Consider the following code:
CREATE TABLE #CDM_Code
(
Code varchar(5)
)
INSERT INTO #CDM_Code
SELECT 'C10..' UNION ALL
SELECT 'R102.' UNION ALL
SELECT 'R10E.' UNION ALL
SELECT 'G2' UNION ALL
SELECT 'G20'
SELECT
1 as Tag,
NULL as Parent,
NULL As [EncounterCodeFilters!1],
NULLas [Code!2],
NULLas [Code!2!Dictionary],
NULLas [Code!2!SubHierarchy]
UNION ALL
SELECT
2 As Tag,
1 As Parent,
NULL As [EncounterCodeFilters!1],
Code As [Code!2],
'READ_V2' As [Code!2!Dictionary],
CASE LEN(Code)
WHEN 5 THEN 'false'
ELSE 'true'
END As [Code!2!SubHierarchy]
FROM #CDM_Code
ORDER BY [EncounterCodeFilters!1], [Code!2!SubHierarchy], [Code!2]
FOR XML EXPLICIT
DROP TABLE #CDM_Code
If you run that through query analyzer I get a formatted XML file, almost the way I want it.
But, being a bit picky, I wanted to order the results differently so I changed the ORDER BY clause as follows:
ORDER BY [EncounterCodeFilters!1], [Code!2!SubHierarchy] DESC, [Code!2]
and I get this error message:
Msg 6833, Level 16, State 1, Line 13
Parent tag ID 1 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set.
Any clues? A search of web threw up some ideas, but none of them useful.
Cheers
-- Mike
September 24, 2008 at 11:06 am
The FOR XML EXPLICIT needs to get the data "ordered" as it should be in the XML output. For better or worse, your ordering scheme is opening up a row at level 2 outside or before a row is open at level1.
Take the FOR XML EXPLICIT out, and you'll see that the "root" tag isn't getting "top billing".
If this is the entire for XML statement, add TAG as the first item in the ORDER BY.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 25, 2008 at 2:49 am
Thanks Matt. Works a treat.
Why is it always the simpliest things that cause grief?
Cheers
-- Mike
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply