XQuery Question
Hi all
i've a question about quering an xml document contains a multilevel xml nodes and i want to retrive only the first level without all the subnodes
below is a snippet of the document
-<CfgPlace DBID="2907" tenantDBID="101" ownerDBID="101" name="Place No 1" state="1"> <CfgPlaceDNlinkDBID="6097" />
<CfgPlaceDNlinkDBID="6098" />
</DNDBIDs>
</CfgPlace>
-<CfgPlace DBID="2900" tenantDBID="101" ownerDBID="101" name="Place No 2" state="1"> <CfgPlaceDNlinkDBID="6115" />
<CfgPlaceDNlinkDBID="6116" />
</DNDBIDs>
-<list_pair key="_WIZARD_"> <str_pairvalue="CfgWizard_Basic.CfgPlaceWizard" key="_ProgID_" />
</list_pair>
</userProperties>
</CfgPlace>
</xml>
I wand the output of the query to retrive the folowing
-<CfgPlace DBID="2907" tenantDBID="101" ownerDBID="101" name="Place No 1" state="1"> -<CfgPlace DBID="2900" tenantDBID="101" ownerDBID="101" name="Place No 2" state="1"> </CfgPlace>
</xml>
Appreciate your help
[15468 byte] By [
Eisa] at [2008-3-3]
Dear Elsa
Note that I moved the thread to a more appropriate Forum group.
What you want is to filter out subtrees. In XQuery 1.0, you have to do this using deconstruction/reconstruction as in:
declare
@x xml set
@x = N'<xml>
<CfgPlace DBID="2907" tenantDBID="101" ownerDBID="101" name="Place No 1" state="1">
<DNDBIDs>
<CfgPlaceDN linkDBID="6097" />
<CfgPlaceDN linkDBID="6098" />
</DNDBIDs>
</CfgPlace>
<CfgPlace DBID="2900" tenantDBID="101" ownerDBID="101" name="Place No 2" state="1">
<DNDBIDs>
<CfgPlaceDN linkDBID="6115" />
<CfgPlaceDN linkDBID="6116" />
</DNDBIDs>
<userProperties>
<list_pair key="_WIZARD_">
<str_pair value="CfgWizard_Basic.CfgPlaceWizard" key="_ProgID_" />
</list_pair>
</userProperties>
</CfgPlace>
</xml>' select
@x.query('<xml>{
for $p in /xml/CfgPlace
return
<CfgPlace>{$p/@*}</CfgPlace>
}</xml>') You can also use the .nodes() method to decompose the XML into a row per CfgPlace, then use the value() method to extract the values out of the attributes and then use FOR XML PATH to recompose the information again.
Best regards
Michael
PS: It is normally considered bad style to name elements with 'xml' since names starting with xml are reserved by the XML specification.
