I am trying to check if the value of a node exists in XML by using SQL. When running the first query, I am able to correctly return results, but the second option does not return results. Is it possible to check if a checkbox with a specific UniqueName exists?

drop table if exists #tempLoad
create table #tempLoad (
    ValueString xml
)

insert into #tempLoad values (
    '<Form ObjectID="1" UniqueName="Form">
        <Page ObjectID="35" UniqueName="OtherInfo" StartText="&lt;div align=&quot;center&quot;&gt;&#xA;&lt;ul id=&quot;ProgressBarDescription&quot; class=&quot;hiddenOffscreen sr-only hidden&quot;&gt;&#xA;&lt;li&gt;Contact Information: Step 1 of 4.&lt;/li&gt;&#xA;&lt;li&gt;Benefit Information: Step 2 of 4.&lt;/li&gt;&#xA;&lt;li&gt;Other Information: Step 3 of 4. Current step.&lt;/li&gt;&#xA;&lt;li&gt;Review and Submit: Step 4 of 4.&lt;/li&gt;&#xA;&lt;/ul&gt;&#xA;&lt;/div&gt;&#xA;&lt;h1&gt;Other Information&lt;/h1&gt;" PreviousText="&lt; Back" NextText="Continue" EditText="Edit" SaveText="Save" CancelText="Cancel" HideCancel="false" FriendlyName="Other Info">
            <Group UniqueName="EthnicityandRaceGroup" ObjectID="1112">
                <CheckBox Align="Below" UniqueName="Ethnicity" Required="false" ObjectID="1114"></CheckBox>
            </Group>
        </Page>
    </Form>'
)

select * from #tempLoad


select *
from #tempLoad a with (nolock)
where a.ValueString.exist('//Form/Page/*[@UniqueName="EthnicityandRaceGroup"]') = 1

select *
from #tempLoad a with (nolock)
where a.ValueString.exist('//Form/Page/*[@UniqueName="Ethnicity"]') = 1

I don't receive any error messages for the latter of the two select statements, it just doesn't return any results.

CodePudding user response：

Found the answer:

select *
from #tempLoad a with (nolock)
where a.ValueString.exist('//Form//*[@UniqueName="Race"]') = 1