May 2, 2009 at 8:09 am
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 ?
May 2, 2009 at 9:35 am
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
How to Post Data/Code to get the best Help How to Post Performance Problems
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply