Xquery

• chatwithkrishan

  Ten Centuries

  Points: 1056

  More actions

  May 2, 2009 at 8:09 am

  #137222

  Hi,

  I m facing eror in Xquery

  CREATE TABLE [dbo].[Config](

  [con_id] [int] NULL,

  [range_xml] [xml] NULL

  ) ON [PRIMARY]

  This xml wil be stored in range_xml

  I want to get the “Column” by loop based on the RangeID

  And I want to assign the value into a local variable.

  DECLARE @count int

  SET @count = 1

  WHILE(@count <= 2)select range_xml.value('Ranges/Range/@RangeType','VARCHAR(20)') from Config
  WHERE range_xml.exist('/Ranges/Range[@RangeID =sql:variable("@count")]')

  But I got error.

  How to get the value ?

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  May 2, 2009 at 9:35 am

  #987722

  Hi

  As first, you should XML with "[ code="xml" ]" and "[ /code ]" (without the quotes and spaces). Otherwise it will not appear in forum

  As second, the error message would be helpful ;-). I just tried and the error I get is:

  Msg 2389, Level 16, State 1, Line 16

  XQuery [@Config.range_xml.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

  If you get the same it does not depend on the "exist" or the sql:variable. The reason is that your XML contains two Range items. The "exist" is the criterion for the row not for the returned XML.

  Depending on your statement you have to surround your "value" XPath with "(...)[1]" to get only one item. But this would return "Gender":

  DECLARE @Config TABLE (

  con_id int NULL,

  range_xml xml NULL

  )

  INSERT INTO @Config

  SELECT 1, '

  '

  DECLARE @id INT

  SELECT @id = 1

  SELECT

  range_xml.value('(Ranges/Range/@RangeType)[1]', 'varchar(20)')

  FROM @Config c

  WHERE range_xml.exist('Ranges/Range[@RangeID=sql:variable("@id")]') = 1

  I think this could return the correct result you are looking for:

  DECLARE @Config TABLE (

  con_id int NULL,

  range_xml xml NULL

  )

  INSERT INTO @Config

  SELECT 1, '

  '

  DECLARE @id INT

  SELECT @id = 1

  SELECT

  range_xml.value('(Ranges/Range[@RangeID=sql:variable("@id")]/@RangeType)[1]', 'varchar(20)')

  FROM @Config c

  WHERE range_xml.exist('Ranges/Range[@RangeID=sql:variable("@id")]') = 1

  Greets

  Flo