Query an XML field - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Query an XML field

Author

Topic

Harry C
Posting Yak Master

148 Posts

Posted - 2011-01-19 : 18:15:59

I have worked on this all day and still have not figured this out. Any help is appreciated. I have an XML file like follows

<DocumentElement>
<index.xml>
<Filename>MS-43656.tif</Filename>
<ScannedDate>3/7/2008</ScannedDate>
</index.xml>
<index.xml>
<Filename>MS-43681.tif</Filename>
<ScannedDate>3/6/2008</ScannedDate>
</index.xml>
<index.xml>
<Filename>MS-43807.tif</Filename>
<ScannedDate>3/6/2008</ScannedDate>
</index.xml>
</DocumentElement>

What I want to do is search on a string like 'MS-43656.tif'

and return just the rows I want
MS-43656.tif
3/7/2008

How can i get this?

SELECT FieldValues.value('(/DocumentElement/index.xml/Filename/node())[5]', 'nvarchar(max)')
FROM projectFields

I dont know, or want to have to know the node index to get the data. Any help is appreciated. Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-19 : 18:43:42

You mean something like this?

DECLARE	@Sample TABLE
	(
		RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
		Data XML NOT NULL
	)

INSERT	@Sample
	(
		Data
	)
VALUES	('<DocumentElement><index.xml><Filename>MS-43656.tif</Filename><ScannedDate>3/7/2008</ScannedDate></index.xml><index.xml><Filename>MS-43681.tif</Filename><ScannedDate>3/6/2008</ScannedDate></index.xml><index.xml><Filename>MS-43807.tif</Filename><ScannedDate>3/6/2008</ScannedDate></index.xml></DocumentElement>')

DECLARE	@FileName VARCHAR(MAX) = 'MS-43807.tif'

SELECT		s.RowID,
		n.value('.', 'varchar(max)') AS Filename,
		n.value('../ScannedDate[1]', 'DATE') AS ScannedDate
FROM		@Sample AS s
CROSS APPLY	s.Data.nodes('//Filename[. = sql:variable("@FileName")]') AS f(n)

N 56°04'39.26"
E 12°55'05.63"

Harry C
Posting Yak Master

148 Posts

Posted - 2011-01-19 : 18:54:15

Perfect. Is there anyway I can do a LIKE search? Rather than a first variable = ?

Thanks so much!!

HC

Harry C
Posting Yak Master

148 Posts

Posted - 2011-01-19 : 22:27:24

Got it, thanks!




SELECT		s.projectFieldID,
		n.value('.', 'varchar(max)') AS Filename,
		n.value('../ScannedDate[1]', 'DATE') AS ScannedDate
FROM		projectFields AS s
CROSS APPLY s.FieldValues.nodes('//Filename') AS f(n)
WHERE n.value('.', 'varchar(max)') LIKE '%MS-43656%'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-20 : 01:25:59

[code]SELECT s.projectFieldID,
n.value('.', 'varchar(max)') AS Filename,
n.value('../ScannedDate[1]', 'DATE') AS ScannedDate
FROM projectFields AS s
CROSS APPLY s.FieldValues.nodes('//Filename') AS f(n)
WHERE f.n.exist('text() [contains(. , sql:variable("@FileName"))]') = 1[/code]

N 56°04'39.26"
E 12°55'05.63"

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2011-01-20 : 06:05:33

quote:

Originally posted by Peso

SELECT		s.projectFieldID,
		n.value('.', 'varchar(max)') AS Filename,
		n.value('../ScannedDate[1]', 'DATE') AS ScannedDate
FROM		projectFields AS s
CROSS APPLY	s.FieldValues.nodes('//Filename') AS f(n)
WHERE 		f.n.exist('text() [contains(. , sql:variable("@FileName"))]') = 1

N 56°04'39.26"
E 12°55'05.63"

Good one