December 19, 2006 at 11:19 am
I am a newbie using XML and XQuery and am having some problems getting the results that I expect. I have a table with an XML Column. The data being stored in the column is in this format (the xml tags have been replaced with square brackets for viewing purposes) :
[r]
[StringData cn="FirstName" v="Sarika" /]
[StringData cn="LastName" v="Agarwal" /]
[StringListData cn="Pets"]
[StringData t="Cat" /]
[StringData t="Dog" /]
[/StringListData]
[/r]
I'm trying to write a query to get the rows where the XML data is queried. I want to return all of the data for the row including the whole XML field. When I use the following query, I'm getting the rows back that have Sarika as the @v-2 value:
SELECT DataSetRowID as RowID, VersionPersistID, XMLValues
FROM tblDataSetValues
WHERE XMLValues.exist ('//StringData[@v="Sarika"]') = 1
But I really need to be able to query both values of an attribute, as follows:
SELECT DataSetRowID as RowID, VersionPersistID, XMLValues
FROM tblDataSetValues
WHERE XMLValues.exist ('//StringData[@cn="FirstName"] and //StringData[@v="Sarika"]') = 1
When I run this query, I get all of the records that have the @cn attribute equal to "FirstName" even if the @v-2 value does not equal "Sarika". It appears that it is not using the and as I expect it to.
I would appreciate any help you can provide.
Thanks!
Wendy Schuman
December 22, 2006 at 8:00 am
This was removed by the editor as SPAM
December 27, 2006 at 8:27 am
Maybe you already solved your issue. The following query works:
SELECT
DataSetRowID as RowID, VersionPersistID, XMLValues
FROM
tblDataSetValues
WHERE
XMLValues.exist ('//StringData[1][@cn="FirstName"]')=1 and XMLValues.exist('//StringData[1][@v="Sarika"]') = 1
OR XMLValues.exist ('//StringData[2][@cn="FirstName"]')=1 and XMLValues.exist('//StringData[2][@v="Sarika"]') = 1
January 2, 2007 at 10:28 am
Thanks for your reply. I did get that to work as well, but we are trying to dynamically create the XMLValues.exist clause and pass it in to the stored procedure as one string.
We ended up changing how our XML document was formatted and instead of using attributes, we used elements and then I used the for statement like this:
[r]
[StringData]
[cn]FirstName[/cn]
[v]Sarika[/v]
[/StringData]
[StringData]
[cn]LastName[/cn]
[v]Agarwal[/v]
[/StringData]
[StringListData]
[cn]Pets[/cn]
[ck]Cat[/ck]
[ck]Dog[/ck]
[/StringListData]
[/r]
And then I rewrote the query to use the for statement of the xml FLWOR statement.
The query then is like this:
DECLARE @QueryString as varchar(max)
SET @QueryString = 'for $WC in /r where($WC/StringData/cn = "FirstName" and $WC/StringData/v = "Sarika" and $WC/StringData/cn = "LastName" and $WC/StringData/v = "Agarwal") return $WC'
DECLARE @SQLStatement varchar(max)
set @SQLStatement = 'SELECT ThisID, ThatID, XMLValues
FROM tblTable
WHERE XMLValues.exist('''+ @QueryString + ''') = 1'
EXEC(@SQLStatement)
I still have some additional work to do to make it work with everything we want it to, but this is a start.
Wendy Schuman
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply