struggling with XML namespace nodes query

fteamdns

New Member
I'm struggling to get my head around XML namespace query when using XML nodes - I've read other similar questions here and an article here but cannot apply this to my specific query.I have a larger version of this sample XML:SELECT CAST('<Navigator clsid="{ABF8EB05-610D-11D3-A53E-684F745C}"> <Version>7</Version> <NavigatorNodes clsid="{0484ACB1-78F5-48D7-A26B-3A0ACD3A5F91}"> <NavigatorNode clsid="{59AB61BD-7833-4954-94C2-B6D737E3DD6F}"> <Indent>0</Indent> <Caption>Contact</Caption> <Selected>0</Selected> <ViewType>0</ViewType> <ViewSubType>0</ViewSubType> <ShowTotals>0</ShowTotals> <RowHeight>0</RowHeight> <Complement>0</Complement> <FreezeFirst>0</FreezeFirst> <a1:QueryFilter xmlns:a2="http://www.ascenttechnology.co.uk/schemas/entities" xmlns:xsi="http://www.w3.org/21/XMLSchema-instance" xmlns:a1="http://www.ascenttechnology.co.uk/schemas/services" Type="QueryFilter"> <entity_name>Contact</entity_name> <relationship_name /> <relationship_from_entity_name /> <a1:ColumnInfoCollection Type="ColumnInfoCollection" AscentCollection="true"> <__vers>1</__vers> <IgnoreMissingItem>0</IgnoreMissingItem> <ReadOnly>0</ReadOnly> <AllowNulls>0</AllowNulls> <a1:ColumnInfo Type="ColumnInfo"> <attribute_name>state</attribute_name> <width>0</width> </a1:ColumnInfo> <a1:ColumnInfo Type="ColumnInfo"> <attribute_name>current_address_suffix</attribute_name> <width>0</width> </a1:ColumnInfo> </a1:ColumnInfoCollection> <a1:SurveyColumnInfoCollection Type="AttributeInfoCollection" AscentCollection="true"> <__vers>1</__vers> <IgnoreMissingItem>0</IgnoreMissingItem> <ReadOnly>0</ReadOnly> <AllowNulls>0</AllowNulls> </a1:SurveyColumnInfoCollection> <a1:WorkflowColumnInfoCollection Type="AttributeInfoCollection" AscentCollection="true"> <__vers>1</__vers> <IgnoreMissingItem>0</IgnoreMissingItem> <ReadOnly>0</ReadOnly> <AllowNulls>0</AllowNulls> </a1:WorkflowColumnInfoCollection> <a1:ConditionCollection Type="ConditionCollection" AscentCollection="true"> <__vers>1</__vers> <IgnoreMissingItem>0</IgnoreMissingItem> <ReadOnly>0</ReadOnly> <AllowNulls>0</AllowNulls> <a1:Condition Type="Condition"> <attribute_name>class</attribute_name> <description1 /> <description2 /> <attribute_id>0</attribute_id> <not>0</not> <operator>28</operator> <value1>1</value1> <value2 xsi:nil="true" /> <indent_level>0</indent_level> <value1_is_reference>0</value1_is_reference> <value2_is_reference>0</value2_is_reference> </a1:Condition> <a1:Condition Type="Condition"> <attribute_name>do_not_mail</attribute_name> <description1 /> <description2 /> <attribute_id>0</attribute_id> <not>0</not> <operator>0</operator> <value1>True</value1> <value2 xsi:nil="true" /> <indent_level>0</indent_level> <value1_is_reference>0</value1_is_reference> <value2_is_reference>0</value2_is_reference> </a1:Condition> <a1:Condition Type="Condition"> <attribute_name>gone_away</attribute_name> <description1 /> <description2 /> <attribute_id>0</attribute_id> <not>0</not> <operator>0</operator> <value1>True</value1> <value2 xsi:nil="true" /> <indent_level>0</indent_level> <value1_is_reference>0</value1_is_reference> <value2_is_reference>0</value2_is_reference> </a1:Condition> <a1:Condition Type="Condition"> <attribute_name>dp_opt_out</attribute_name> <description1 /> <description2 /> <attribute_id>0</attribute_id> <not>0</not> <operator>0</operator> <value1>True</value1> <value2 xsi:nil="true" /> <indent_level>0</indent_level> <value1_is_reference>0</value1_is_reference> <value2_is_reference>0</value2_is_reference> </a1:Condition> <a1:Condition Type="Condition"> <attribute_name>deceased</attribute_name> <description1 /> <description2 /> <attribute_id>0</attribute_id> <not>0</not> <operator>0</operator> <value1>True</value1> <value2 xsi:nil="true" /> <indent_level>0</indent_level> <value1_is_reference>0</value1_is_reference> <value2_is_reference>0</value2_is_reference> </a1:Condition> <a1:Condition Type="Condition"> <attribute_name>deleted</attribute_name> <description1 /> <description2 /> <attribute_id>0</attribute_id> <not>0</not> <operator>0</operator> <value1>True</value1> <value2 xsi:nil="true" /> <indent_level>0</indent_level> <value1_is_reference>0</value1_is_reference> <value2_is_reference>0</value2_is_reference> </a1:Condition> <a1:Condition Type="Condition"> <attribute_name>Date_Of_Birth</attribute_name> <description1 /> <description2 /> <attribute_id>0</attribute_id> <not>0</not> <operator>19</operator> <value1>2010-06-23T::.+:</value1> <value2 xsi:nil="true" /> <indent_level>0</indent_level> <value1_is_reference>0</value1_is_reference> <value2_is_reference>0</value2_is_reference> </a1:Condition> <a1:Condition Type="Condition"> <attribute_name>home_address1</attribute_name> <description1 /> <description2 /> <attribute_id>0</attribute_id> <not>0</not> <operator>26</operator> <value1 xsi:nil="true" /> <value2 xsi:nil="true" /> <indent_level>0</indent_level> <value1_is_reference>0</value1_is_reference> <value2_is_reference>0</value2_is_reference> </a1:Condition> <a1:Condition Type="Condition"> <attribute_name>home_postcode</attribute_name> <description1 /> <description2 /> <attribute_id>0</attribute_id> <not>0</not> <operator>26</operator> <value1 xsi:nil="true" /> <value2 xsi:nil="true" /> <indent_level>1</indent_level> <value1_is_reference>0</value1_is_reference> <value2_is_reference>0</value2_is_reference> </a1:Condition> <a1:Condition Type="Condition"> <attribute_name>home_address1</attribute_name> <description1 /> <description2 /> <attribute_id>0</attribute_id> <not>0</not> <operator>0</operator> <value1 /> <value2 xsi:nil="true" /> <indent_level>0</indent_level> <value1_is_reference>0</value1_is_reference> <value2_is_reference>0</value2_is_reference> </a1:Condition> <a1:Condition Type="Condition"> <attribute_name>home_postcode</attribute_name> <description1 /> <description2 /> <attribute_id>0</attribute_id> <not>0</not> <operator>0</operator> <value1 /> <value2 xsi:nil="true" /> <indent_level>1</indent_level> <value1_is_reference>0</value1_is_reference> <value2_is_reference>0</value2_is_reference> </a1:Condition> </a1:ConditionCollection> <a1:OrderByCollection Type="OrderByCollection" AscentCollection="true"> <__vers>1</__vers> <IgnoreMissingItem>0</IgnoreMissingItem> <ReadOnly>0</ReadOnly> <AllowNulls>0</AllowNulls> </a1:OrderByCollection> <SelectedArticles xsi:nil="true" /> <groupby_field_name /> <reporting_currency>52</reporting_currency> <Negate_Filter>0</Negate_Filter> <Survey_Id>----</Survey_Id> <Survey_Name xsi:nil="true" /> <Workflow_Id>----</Workflow_Id> <Workflow_Name xsi:nil="true" /> <XPath>0</XPath> <locale>0</locale> </a1:QueryFilter> </NavigatorNode> </NavigatorNodes></Navigator>' AS XML) as xmldataAnd have been trying queries to get to the information contained within the a1:Condition Node. I've tried queries such as:;WITH XMLNAMESPACES('http://www.ascenttechnology.co.uk/schemas/services' AS a1,'http://www.ascenttechnology.co.uk/schemas/entities' AS a2,DEFAULT 'http://www.w3.org/21/XMLSchema-instance')SELECT #xmldata.*,ref.value('attribute_name[1]','NVARCHAR(50)') as attribute_nameFROM #xmldataCROSS APPLY xmldata.nodes('/Navigator/NavigatorNodes/NavigatorNode/a1:QueryFilter/a1:ConditionCollection/a1:Condition') T(ref)But they're always coming back blank when I get near the namespaced nodes - I'm able to pull data from the nodes all the way up to '/Navigator/NavigatorNodes/NavigatorNode' but then have no luck.Thanks!
 
Back
Top