xml explicit siblings - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

xml explicit siblings

Author

Topic

AvdA
Starting Member

5 Posts

Posted - 2001-12-19 : 01:35:29

An easy one for those of you who have had your head around XML explicit longer than me...

What I would like to achieve is an XML output that is essentially like:
<RootElement>
<OutsideLevel1 type = "the OL1 type goes here">
<OL1Sibling1>the OL1Sibling1 text goes here</OL1Sibling1>
<OL1Sibling2>the OL1Sibling2 text goes here</OL1Sibling2>
<OL1Sibling3>
<Child1ofOL1Sibling3>the Child1ofOL1Sibling3 text goes here</Child1ofOL1Sibling3>
<Child2ofOL1Sibling3>the Child2ofOL1Sibling3 text goes here</Child2ofOL1Sibling3>
</OL1Sibling3>
<OL1Sibling4>the OL1Sibling4 text goes here</OL1Sibling4>
<OL1Sibling5>the OL1Sibling5 text goes here</OL1Sibling5>
</OutsideLevel1>
</RootElement>

My sample SQL looks like this (no DB required):

SELECT 1 AS TAG, 0 AS PARENT,
NULL AS [RootElement!1!OutsideLevel1],
NULL AS [OutsideLevel1!2!type],
NULL AS [OutsideLevel1!2!OL1Sibling1!element],
NULL AS [OutsideLevel1!2!OL1Sibling2!element],
NULL AS [OutsideLevel1!2!OL1Sibling3!element],
NULL AS [OL1Sibling3!3!Child1ofOL1Sibling3!element],
NULL AS [OL1Sibling3!3!Child2ofOL1Sibling3!element],
NULL AS [OutsideLevel1!4!OL1Sibling4!element],
NULL AS [OutsideLevel1!4!OL1Sibling5!element]

UNION ALL

SELECT 2 , 1 ,
NULL,
'the OL1 type goes here',
'the OL1Sibling1 text goes here',
'the OL1Sibling2 text goes here' ,
NULL,
NULL,
NULL,
NULL,
NULL

UNION ALL

SELECT 3 AS TAG, 2 AS PARENT,
NULL,
'the OL1 type goes here',
'the OL1Sibling1 text goes here',
'the OL1Sibling2 text goes here' ,
NULL,
'the Child1ofOL1Sibling3 text goes here',
'the Child2ofOL1Sibling3 text goes here',
NULL,
NULL

UNION ALL

SELECT 4 AS TAG, 2 AS PARENT,
NULL,
'the OL1 type goes here',
'the OL1Sibling1 text goes here',
'the OL1Sibling2 text goes here' ,
NULL,
NULL,
NULL,
'the OL1Sibling4 text goes here',
'the OL1Sibling5 text goes here'

order by TAG
FOR XML EXPLICIT

which results in:
<RootElement>
<OutsideLevel1 type = "the OL1 type goes here">
<OL1Sibling1>the OL1Sibling1 text goes here</OL1Sibling1>
<OL1Sibling2>the OL1Sibling2 text goes here</OL1Sibling2>
<OL1Sibling3>
<Child1ofOL1Sibling3>the Child1ofOL1Sibling3 text goes here</Child1ofOL1Sibling3>
<Child2ofOL1Sibling3>the Child2ofOL1Sibling3 text goes here</Child2ofOL1Sibling3>
</OL1Sibling3>
<OutsideLevel1>
<OL1Sibling4>the OL1Sibling4 text goes here</OL1Sibling4>
<OL1Sibling5>the OL1Sibling5 text goes here</OL1Sibling5>
</OutsideLevel1>
</OutsideLevel1>
</RootElement>

As you can see there is an extra OutsideLevel1 tag that has slipped in and pushed OL1Siblings4 and 5 in a level.

I have tried a variety of orderings but I haven't managed to get one to produce the desired output.

Actually the real XML will have a whole lot more OutsideLevels with contents similarly formatted to this one.

Thanks AvdA

AvdA
Starting Member

5 Posts

Posted - 2002-01-03 : 18:02:54

Ok... I notice there are no replies so I'll add the answer here with some tips for those of you facing the same dilemma.

A. How I solved the problem. (step 1, step 2, step 3)
B. The solution (It doesn't seem to fit on this page so I'll post it separately).

Cheers, AvdA.

A step 1. Using the xsd schema notation I knocked up a schema that would produce the output I required. (See that this has is linked to fields in the database - pubs - which are irrelevant but useful on the way to the result. I will later edit the SQL itself to get the data in a more efficient way than the schema is able to guess...).
ie.
<xsd:schema id="RootElement" xmlns=""
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="OutsideLevel1" sql:relation="authors" sql:key-fields="au_id">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="OL1Sibling1" sql:field="au_id" type="xsd:string" />
<xsd:element name="OL1Sibling2" sql:field="au_fname" type="xsd:string" />
<xsd:element name="OL1Sibling3" sql:is-constant="1">
<xsd:complexType>
<xsd:all>
<xsd:element name="Child1ofOL1Sibling3" sql:field="address" type="xsd:string" minOccurs="0" />
<xsd:element name="Child2ofOL1Sibling3" sql:field="city" type="xsd:string" minOccurs="0" />
</xsd:all>
</xsd:complexType>
</xsd:element>
<xsd:element name="OL1Sibling4" sql:field="au_id" type="xsd:string" />
<xsd:element name="OL1Sibling5" sql:field="au_fname" type="xsd:string" />
</xsd:sequence>
<xsd:attribute name="type" sql:field="au_id"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>

This schema can be executed using the template containing...
<RootElement xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:xpath-query mapping-schema="..\simplepersons.xsd">
OutsideLevel1
</sql:xpath-query>
</RootElement>

A step 2. Run the schema via the template and catch the SQL generated using SQLServer's Profiler.

A step 3. Edit the SQL statement, substituting the appropriate values for the selects and from clauses but maintaining the structure provided by the hidden ordering columns and the Order by clause. To keep this eg simple I have just used 'text values' and no From clause but I intend to replace each piece with column names and the appropriate table names and joins.

B.
See next posting.

AvdA
Starting Member

5 Posts

Posted - 2002-01-03 : 18:04:23

Continued from previous post...

B. (The solution).

select 1 as TAG,0 as parent,
'the OL1Sibling1 text goes here' as [OutsideLevel1!1!OL1Sibling1OrderingKey!hide],
'the OL1Sibling1 text goes here' as [OutsideLevel1!1!OL1Sibling1!element],
'the OL1Sibling2 text goes here' as [OutsideLevel1!1!OL1Sibling2!element],
'the OL1 type goes here' as [OutsideLevel1!1!type],
NULL as [OL1Sibling3!2!OL1Sibling3OrderingKey!hide],
NULL as [Child1ofOL1Sibling3!3!Child1ofOL1Sibling3OrderingKey!hide],
NULL as [Child1ofOL1Sibling3!3!],
NULL as [Child2ofOL1Sibling3!4!Child2ofOL1Sibling3OrderingKey!hide],
NULL as [Child2ofOL1Sibling3!4!],
NULL as [OL1Sibling4!5!OL1Sibling4OrderingKey!hide],
NULL as [OL1Sibling4!5!],
NULL as [OL1Sibling5!6!OL1Sibling5OrderingKey!hide],
NULL as [OL1Sibling5!6!]

union all
select 2,1,
'the OL1Sibling1 text goes here' ,
'the OL1Sibling1 text goes here' ,
NULL,
NULL,1,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL

union all
select 3,2,
'the OL1Sibling1 text goes here' ,
'the OL1Sibling1 text goes here' ,
NULL,
NULL,1,2,'the Child1ofOL1Sibling3 text goes here',NULL,
NULL,NULL,NULL,NULL,NULL

union all
select 4,2,
'the OL1Sibling1 text goes here' ,
'the OL1Sibling1 text goes here' ,
NULL,
NULL,1,NULL,NULL,3,
'the Child2ofOL1Sibling3 text goes here',NULL,NULL,NULL,NULL

union all
select 5,1,
'the OL1Sibling1 text goes here' ,
'the OL1Sibling1 text goes here' ,
NULL,
NULL,NULL,NULL,NULL,NULL,
NULL,4,'the OL1Sibling4 text goes here',NULL,NULL

union all
select 6,1,
'the OL1Sibling1 text goes here' ,
'the OL1Sibling1 text goes here' ,
NULL,
NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,5,'the OL1Sibling5 text goes here'

order by 3,4,14,12,7,10,8,2,1 for xml explicit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources