May 1, 2013 at 4:00 am
Hi,
I have a complicated set of schema files for which I am trying to generate a single XML file. The initial schema documents allow a choice of element, each of which has it's own set of schema files.
eg:
<xs:sequence>
<xs:choice minOccurs="1" maxOccurs="1">
<xs:element name="Opt1" type="Opt1Type" minOccurs="0" maxOccurs="1"/>
<xs:element name="Opt2" type="Opt2Type" minOccurs="0" maxOccurs="1"/>
<xs:element name="Opt3" type="Opt3Type" minOccurs="0" maxOccurs="1"/>
</xs:choice>
</xs:sequence>
Using FOR XML EXPLICIT I had the following code excerpt:
SELECT
1 AS Tag,
0 AS Parent,
...
NULL AS [Id!2!root],
NULL AS [RecordCount!3!value],
NULL AS [ReportingPeriodStartDate!4],
NULL AS [ReportingPeriodEndDate!5],
NULL AS [Record!6],
NULL AS [Record!6!SORT!HIDE],
NULL AS [Id!7!root],
NULL AS [Opt1!8],
NULL AS [Opt1!8!SORT!HIDE],
...
I had been generating a separate XML file for each option choice (using the above code and it worked fine), but now need to pull everything as a single file. The problem is that I can't redefine tag 8 as "Opt2" as it is already defined as "Opt1". If I define it as tag 9 things get massively complicated. There are 12 options in total and each of them has 400 data items hanging off it, some of them generic and others specific to the option. If I number the "Opt1", "Opt2" etc elements separately then I have to code all of the data items to point at the appropriate parent tag. I don't want to write 400 * 12 blocks of code if there is another way around it.
I am considering the approach of using CASE statements to select the PARENT tag numbers dynamically but that seems over-complicated. Does anyone have any ideas how to write this code more efficiently and deal with the schema choices please?
Thanks in advance for any help.
May 3, 2013 at 3:47 am
Have now worked this out. The solution was to forget EXPLICIT altogether and go with XML PATH and nested XML SELECT statements.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply