November 9, 2011 at 10:38 pm
I need to check whether XML text contains "My performance has gone no where" in "score match_option" but below query is not working
DECLARE @doc XML
/* Populate the XML document */
SELECT @doc = N'<?xml version = "1.0" encoding = "UTF-16"?>
<QUESTIONSCRadio xmlns="http://schema.cvent.com/surveys/QuestionDetail" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" other_label="" choice_sort_order_id="1">
<choice>My performance has improved</choice>
<choice>My performance has gone nowhere</choice>
<choice>My performance has not improved</choice>
<choice>I did not work here last year</choice>
<score match_option="any" row_label="My performance has improved" scoreValue="2.00" />
<score match_option="any" row_label="My performance has gone no where" scoreValue="2.00" />
<score match_option="any" row_label="My performance has not improved" scoreValue="2.00" />
<score match_option="any" row_label="I did not work here last year" scoreValue="2.00" />
</QUESTIONSCRadio>';
SELECT @doc;
SELECT @doc.value
('declare namespace ns="http://schema.cvent.com/surveys/QuestionDetail";(/ns:QUESTIONSCRadio/ns:score match_option)[2]',
'nvarchar(200)') = 'My performance has gone no where'
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 11, 2011 at 1:20 am
This was removed by the editor as SPAM
November 16, 2011 at 6:01 am
Do not know what is the status of your problem but you cannot do such comparison. Besides you are not using the right attribute to search for. "score match_option" is not an attribute. "score" was an element and then "row_label" was its attribute for which the string "My performance has gone no where" should be searched. Below query may make you understand what was wrong
DECLARE @doc XML
/* Populate the XML document */
SELECT @doc = N'<?xml version = "1.0" encoding = "UTF-16"?>
<QUESTIONSCRadio xmlns="http://schema.cvent.com/surveys/QuestionDetail" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" other_label="" choice_sort_order_id="1">
<choice>My performance has improved</choice>
<choice>My performance has gone nowhere</choice>
<choice>My performance has not improved</choice>
<choice>I did not work here last year</choice>
<score match_option="any" row_label="My performance has improved" scoreValue="2.00" />
<score match_option="any" row_label="My performance has gone no where" scoreValue="2.00" />
<score match_option="any" row_label="My performance has not improved" scoreValue="2.00" />
<score match_option="any" row_label="I did not work here last year" scoreValue="2.00" />
</QUESTIONSCRadio>';
SELECT @doc.value('declare namespace ns="http://schema.cvent.com/surveys/QuestionDetail";(/ns:QUESTIONSCRadio/ns:score/@row_label)[2]', 'nvarchar(200)')
WHERE @doc.exist(' declare namespace ns="http://schema.cvent.com/surveys/QuestionDetail";/ns:QUESTIONSCRadio/ns:score[@row_label="My performance has gone no where"]') = 1
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply