October 10, 2011 at 3:39 pm
I have been trying to figure out the correct syntax for a query, but I have not come up with something that gives me what I want.
Each row in a table contains a formatted XML string. Essentially, the string is of the format:
<dataDef>
<facility name="building 1A">
<element name=...../>
<element name=..../>
<feature name="feature1">
<element name=..../>
<element name=.../>
</feature>
<feature name="feature2">
<element name=..../>
<element name=.../>
</feature>
<feature name="feature3">
<element name=..../>
<element name=.../>
</feature>
</facility>
<facility name="building 2A">
<element name=...../>
<element name=..../>
<feature name="feature1">
<element name=..../>
<element name=.../>
</feature>
<feature name="feature2">
<element name=..../>
<element name=.../>
</feature>
<feature name="feature3">
<element name=..../>
<element name=.../>
</feature>
</facility>
</dataDef>
There can be 1 to n facilities and each facility can have 1 to feature elements.
The query I am trying to put together is one that will return all of the facilities in the string and include only feature named "feature3" with the facility data. What I came up with that fails when memory is exhausted is below. The table name is dataDef and the column name contained the typed XML is info
declare @dispValue xml
@featureName varchar(20)
set @featureName='feature3'
select @dispVal =
(select info.query('(//facility)[1]'),
(select info.query('
(
for $i in (//feature)
let $k := ($i/@name)
while $k = fn:string(sql:variable("@featureName"))
return ($i))[1]')
from dataDef
for XML path (''))
from dataDef
from xml path (''), root('dataDef'))
I think I am actually ending up with a cartesian product, but I am not clear how to correct the query.
October 11, 2011 at 2:27 am
This was removed by the editor as SPAM
October 11, 2011 at 10:06 am
Thank-you for the reply; no I have not tried nodes.
However, I was able to figure out the syntax I needed to get the facility information, and the desired feature. One thing that I did was group the elements under the facility into complex type so that the facility xsd referenced these complex types:
<xsd:schema
.... <-- remove for clarify
>
<xsd:element name="dataDef">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="facility" minOccurs="0" maxOccurs="unbounded"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="facility">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="basics" minOccurs="1" maxOccurs="1"/>
<xsd:element ref="facilitySpec" minOccurs="1" maxOccurs="1"/>
<xsd:element ref="feature" minOccurs="0" maxOccurs="unbounded"/>
</xsd:sequence>
<xsd:attribute name="name" type="xsd:string" use="required"/>
</xsd:complexType>
</xsd:element>
<xsd:element name="feature">
<xsd:complexType>
<xsd:sequence>
<xsd:element ......../>
<xsd:element ......../>
</xsd:sequence>
<xsd:attribute name="name" type="xsd:string" use="required"/>
</xsd:complexType>
</xsd:element>
< remained of complex types not shown........ />
</xsd:schema>
With the same XML string as originally posted, I wrote the query to get all of the facilities; each with one specific feature:
declare @dispValue xml
@featureName varchar(20)
set @featureName='feature3'
select @dispVal =
(select bb.info.value('(//facility/@name)[1]','char(10)') as '@name',
bb.info.value('(//facility/basics)[1]'),
bb.info.value('(//facility/facilitySpec)[1]'),
(select aa.info.query('
(
for $i in (//feature)
let $k := ($i/@name)
while $k = fn:string(sql:variable("@featureName"))
return ($i))[1]')
from dataDef aa
where aa.<table unique key> = bb.<table unique key>
for XML path (''), type
from dataDef bb
from xml path ('facility'), root('dataDef'))
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply