Retrieve row based on Element value in xml Column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Retrieve row based on Element value in xml Column

Author

Topic

Evan
Starting Member

4 Posts

Posted - 2009-01-08 : 10:51:37

I have a table with the following definition:

ID int (primary key)
DeviceTypeID int (foreign key)
OrganizationID int (foreign key)
Attributes xml(CONTENT dbo.DeviceAttributes)
RowCreatedDate datetime

I have the xml column bound to a XML SCHEMA COLLECTION called DeviceAttributes in which I have added multiple schemas.

Here is the query I am running:

SELECT
d1.ID,
d1.Attributes.query('
declare namespace ATTR="http://www.somecompany.com/somenamespace/devices/laptop";
for $A in /ATTR:deviceAttributes
where $A/ATTR:laptopID[.="A1234"]
return
$A
') as Attributes
FROM
Device d1

I would like to retrieve the primary key (ID) of the device based on the value of the laptopID value that is in the xml.

However, using the query above, I am getting all rows in the device table, but the xml column is empty except for the row that matches the XQuery. Close, but no cigar. Is there a way to filter the rows returned from the query based on the value of an element in the xml?

Thanks in advance,
Evan

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-08 : 11:01:05

If the Attributes column contains nulls except for row required:

Select * from (

SELECT
d1.ID,
d1.Attributes.query('
declare namespace ATTR="http://www.somecompany.com/somenamespace/devices/laptop";
for $A in /ATTR:deviceAttributes
where $A/ATTR:laptopID[.="A1234"]
return
$A
') as Attributes
FROM
Device d1

) as A
where Attributes is not null

Evan
Starting Member

4 Posts

Posted - 2009-01-08 : 11:04:11

No, unfortunately, the xml column is NOT NULL. My problem is that the query I posted returns all rows in the table, but only the xml for the row that the XQuery matches. I want just the row that the XQuery returns xml for. Is that possible?

Thanks,

Evan

quote:
Originally posted by darkdusky

If the Attributes column contains nulls except for row required:

Select * from (

SELECT
d1.ID,
d1.Attributes.query('
declare namespace ATTR="http://www.somecompany.com/somenamespace/devices/laptop";
for $A in /ATTR:deviceAttributes
where $A/ATTR:laptopID[.="A1234"]
return
$A
') as Attributes
FROM
Device d1

) as A
where Attributes is not null

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 11:15:39

[code]
select *
from
(
SELECT
d1.ID,
d1.Attributes.query('
declare namespace ATTR="http://www.somecompany.com/somenamespace/devices/laptop";
for $A in /ATTR:deviceAttributes
where $A/ATTR:laptopID[.="A1234"]
return
$A
') as Attributes
FROM
Device d1
)t
WHERE Attributes >''
[/code]

Evan
Starting Member

4 Posts

Posted - 2009-01-08 : 11:18:24

Very cool. Now it seems so clear. How efficient is that? Is that the only way?

Hey, thanks a lot.

Evan

quote:

Originally posted by visakh16


select *
from
(
SELECT 
d1.ID,	
d1.Attributes.query('
declare namespace ATTR="http://www.somecompany.com/somenamespace/devices/laptop";
for $A in /ATTR:deviceAttributes
where $A/ATTR:laptopID[.="A1234"]
return
$A
') as Attributes	
FROM
Device d1
)t
WHERE Attributes >''

Evan
Starting Member

4 Posts

Posted - 2009-01-08 : 11:38:46

I got it using the following query. Thanks to all for your quick response!

SELECT
*
FROM
DEVICE
WHERE
Attributes.exist('
declare namespace ATTR="http://some.company.com/some/namespace/devices/attributes/laptop";
/ATTR:deviceAttributes/ATTR:laptopID[.="A1234"]
') = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources