Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Representing choice of elements using FOR XML EXPLICIT Expand / Collapse
Author
Message
Posted Wednesday, May 1, 2013 4:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 30, 2013 3:39 AM
Points: 10, Visits: 48
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.
Post #1448368
Posted Friday, May 3, 2013 3:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 30, 2013 3:39 AM
Points: 10, Visits: 48
Have now worked this out. The solution was to forget EXPLICIT altogether and go with XML PATH and nested XML SELECT statements.
Post #1449102
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse