I would like to generate an XML document using SQL Server 2000's explicit option.
I want something like this for my output:
<root>
<someNode1>
<someNode2 />
</someNode1>
<someNode3>
<someNode4 />
</someNode3>
</root>
The problem is that I end up getting an "originally declared" node being "redeclared" as something else error. In this case, someNode1 is "redeclared" as someNode3. It looks as though it's not possible to create two different elements on the same level. I've not found any samples in documentation that show such a scenario. Anybody have a solution or hack? It seems like this kind of output would be common in the business world.
TIA,
Ian
Hi there,
I'm no expert on this but came across your post whilst being up against a similar problem.
This URL helped me out: http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0902/en/html/dbugen9/00000535.htm
This is a working example derived from something I am working on myself:
SELECT
1 AS Tag,
NULL AS Parent,
1 AS [Item!1!Id],
'Item' AS [Item!1!Name],
1 AS [Item!1!ParentId],
NULL AS [NutritionalBreakdown!2!Calories!element],
NULL AS [NutritionalBreakdown!2!Protein!element]
UNION ALL
SELECT
2,
1,
NULL,
NULL,
NULL,
'test calorie data',
'test protein data'
ORDER BY Tag
FOR XML EXPLICIT
Produces:
<Item Id="1" Name="Item" ParentId="1">
<NutritionalBreakdown>
<Calories>test calorie data</Calories>
<Protein>test protein data</Protein>
</NutritionalBreakdown>
</Item>
Hope that helps!
