XML

• Chandru -734144

  Ten Centuries

  Points: 1040

  More actions

  July 18, 2008 at 5:17 am

  #195512

  Dear all,

  I'm trying to print a particular value from a Xml string .I have tried this coding but i cant get the o/p can any one help me on this....

  the error im getting is

  'The parameters supplied for the function "OpenXML" are not valid '

  declare @xml_hnd varchar(max)

  declare @option_id int

  set @xml_hnd='

  '

  set @option_id =(select option_id from openXML

  (@xml_hnd ,'/Newdataset/table',2)

  with

  ( option_id int

  ))

  print @option_id

  Thanks.

• whatispunk

  Old Hand

  Points: 356

  More actions

  July 18, 2008 at 3:18 pm

  #845275

  Are you using SQL 2005? If so, I would suggest you use the xml datatype rather than openXML.

  declare @xml_hnd xml

  set @xml_hnd='

  <Root>

  <Node id="1" />

  <Node id="2" />

  </Root>

  '

  DECLARE @option_id int

  SELECT @option_id = c.value('.[@id="2"]/@id', 'int')

  FROM @xml_hnd.nodes('/Root/Node') t(c)

  SELECT @option_id

  This will return '2'