Hi,
Try replacing your openxml with the following:
DECLARE @idoc int
DECLARE @doc varchar(300)
SET @doc ='<criticalityquestion>
<shortform><![CDATA[Stay again]]> </shortform>
<optionlabel><![CDATA[Yes;Maybe;No]]> </optionlabel>
<optionvalue><![CDATA[Yes;Maybe;No]]> </optionvalue>
</criticalityquestion>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT *
FROM OPENXML (@idoc, '/criticalityquestion/optionvalue')
WITH (optionvalue varchar(20) '.')
EXEC sp_xml_removedocument @idoc
quote:
Hi,I am trying to extract of a particular CDATA tag from an XML string using OPENXML. The string looks something like this:
<criticalityquestion> <shortform><![CDATA[Stay again]]> </shortform> <optionlabel><![CDATA[Yes;Maybe;No]]> </optionlabel> <optionvalue><![CDATA[Yes;Maybe;No]]> </optionvalue> </criticalityquestion>
I would like to retrieve the values listed after the option value tag. Can somebody help with that?
thanks
Zoya