This is a problem that has been nagging me for a while, and I cannot
figure out how to best solve the problem:
I have a stored procedure that returns multi-level "nested" XML and
inline XDR. My goal is to have a .NET application read the XML into a
DataSet using the inline schema, and the relations automatically
created according to the nesting of the elements.
My query in the stored procedure looks similar to this:
SELECT 1 AS TAG,
NULL AS PARENT,
c.name [Customer!1!Name]
NULL [Details!2!Address]
FROM Customer c
UNION ALL
SELECT 2 AS TAG,
1 AS PARENT,
NULL,
c.address
FROM Customer c
FOR XML EXPLICIT, XMLDATA
The XML that gets returned looks fine, but the XDR does not contain
any relation information. That is, the schema does not restrict how
the <Details> element must be nested within the <Customer> element.
Therefore when I use my .NET app to load the XML into a DataSet (using
ReadMode.Fragment), the relation between the Customer table and the
Details table are not linked in any way.
However, when I load the DataSet using ReadMode.InferSchema and then
write out the schema (with ds.WriteXmlSchema), the XSD indeed contains
the relation information. If I use the XSD schema next time to load
the DataSet, the tables in the DataSet will have the appropriate
relations.
My question is, how can I use XMLDATA in my stored procedure to create
relation information, so that my DatSet can auto-generate the
relations when it loads the XML? More generally, is there a way to
create an inline schema such that I can auto-generate the "nested"
relations in a DataSet?