OPENXML

• rozinar

  Valued Member

  Points: 69

  More actions

  November 13, 2003 at 10:09 am

  #162689

  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

• November 17, 2003 at 12:00 pm

  This was removed by the editor as SPAM

• archimed

  SSC Enthusiast

  Points: 103

  More actions

  November 27, 2003 at 8:08 am

  #483931

  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

  ---

• yousef Ekhtiari

  Ten Centuries

  Points: 1283

  More actions

  December 10, 2003 at 4:58 am

  #485496

  OR Try This:

  SELECT *

  FROM OPENXML (@idoc, '/criticalityquestion/optionvalue',2)

  WITH (optionvalue varchar(20) 'text()')