CODEWITHSUNDEEP
sqlxmlsql-server-2008xsdsql-server-2008-r2
millie
millie

Reputation: 2895

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

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

tblFiles as the following columns:

  1. FileId - (int) primary key
  2. FileName - (nvarchar 255)
  3. 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

Upvotes: 1

Views: 3302

Answers (2)

JohnD
JohnD

Reputation: 14757

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:

26

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

Upvotes: 3

John N
John N

Reputation: 1815

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:

Upvotes: 0

Related Questions