How to do SQL Query for XML data (in SQLServer)?

Question

I have a simple table called tblFiles in SQL Server 2008 R2.

tblFiles as the following columns:

FileId - (int) primary key
FileName - (nvarchar 255)
MetaData - (xml) has been configured with an XML schema for validation.

The schema is as follows:

<?xml version="1.0" encoding="UTF-16"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="FileMetaData">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="CreatedDate" type="xs:time"/>
                <xs:element name="ModifiedDate" type="xs:time"/>
                <xs:element name="AccessDate" type="xs:time"/>
            </xs:sequence>
            <xs:attribute name="Length" type="xs:integer"/>
        </xs:complexType>
    </xs:element>
</xs:schema>

A sample XML metadata entry in a record is:

<?xml version="1.0"?>
<FileMetaData Length="26">
    <CreatedDate>10:13:53.1008</CreatedDate>
    <ModifiedDate>10:14:02.0327</ModifiedDate>
    <AccessDate>10:13:53.1008</AccessDate>
</FileMetaData>

I've populated the database with a load of files and its associated metadata.

What I'm trying to work out is how to write a query that will return return all records that have a length set in the XML that is between X and Y?

How do I navigate the XML in the SQL query?

TIA

JohnD · Accepted Answer · 2011-09-20 12:50:40Z

3

I tested this on your XML:

SELECT MyXmlCol.value('(/FileMetaData/@Length)[1]', 'int')
FROM MyXmlTable
WHERE MyXmlCol.value('(/FileMetaData/@Length)[1]', 'int') BETWEEN 25 AND 30

Output:

You have to remember the [1] to tell the query to grab the first value. The "@" specifies an XML attribute. You can check out the docs on the value() function here:

http://msdn.microsoft.com/en-us/library/ms178030.aspx

edited Sep 20, 2011 at 12:50

answered Sep 20, 2011 at 12:44

JohnD

14.9k4 gold badges42 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

millie Over a year ago

I managed to figure out another way to do this as well using the query: SELECT FileId, FileName, MetaData FROM Files WHERE MetaData.exist('/FileMetaData[@Length > 25]') = 1 AND MetaData.exist('/FileMetaData[@Length < 30]') = 1 I have tried both methods but the one I listed here has much better performance (BTW I have an XML index on the column MetaData).

Community · Accepted Answer · 2017-05-23 12:12:00Z

0

This is from memory, so my syntax might be a little out. It should be something like this:

declare @x int
declare @y int

set @x = 10
set @y = 50

select *
from tblFiles
where MetaData.value('(/FileMetaData/@Length)', 'int') between @x and @y

There are plenty of other SO questions on this kind of topic:

edited May 23, 2017 at 12:12

CommunityBot

11 silver badge

answered Sep 20, 2011 at 12:28

John N

1,81518 silver badges21 bronze badges

Collectives™ on Stack Overflow

How to do SQL Query for XML data (in SQLServer)?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related