SQL Server 2005 XPATH Multi-Node Query

[Mokil]

Hi all,

I'm trying to query an XML file I've brought into a SQL Server database.

The base format of the file is:

<device name="device_name" id="1">
<property name="Manufacturer" value="Microsoft" />
<property name="Model" value="Optical Mouse" />
</device>

I want to be able to obtain the Manufacturer and Model property values relationally, i.e. in the format:

DEVICE ID MANUFACTURER MODEL
1 Microsoft Optical Mouse
etc

I can obtain one of the values but not both. Does anybody have any idea how to do this?
My current code is along the lines:

SELECT TOP 10
manufacturer.device.value('@value', 'varchar(200)') AS [Manufacturer],
model.device.value('@value', 'varchar(200)') AS [Model]
FROM
@xmlDoc.nodes('/device/property[@name = "Manufacturer"]')
Manufacturer (Device),
@xmlDoc.nodes('/device/property[@name = "Manufacturer"]')
Model (Device)

This returns a cartesian - I ideally need to join on the device id, or return a single node set using some sort of or function.

Thanks in advance.

Best regards,
Mokil.

 

[mrdenny]

You'll want something like this.

/*
Load up your data into the @hdoc using sp_xml_preparedocument here
*/

select devicename, Manufacturer, model
	FROM OPENXML(@hdoc, N'/device/property', 1)
	WITH (device sysname '../@id',
		head sysname '@name',
		data sysname '@value',)
	PIVOT
	(
		max(Data)
		FOR head in ([Manufacturer], [Model])
	) as pvt

/*
Close the file here.
*/

This isn't tested but should work with minimal tweaking

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com