Need help selecting an xml node using a parameter

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)

Need help selecting an xml node using a parameter

Author

Topic

tigriswoods
Starting Member

1 Post

Posted - 2009-12-19 : 10:15:24

Hello, I am using Sql Server 2005. I have a table called TestResults, containing a column 'data' defined as xml(CONTENT dbo.TestRecordingSchema)
where TestRecordingSchema is defined as:

USE [myDatabase]
GO
/****** Object: XmlSchemaCollection [dbo].[TestRecordingsSchema] Script Date: 12/09/2009 ******/
CREATE XML SCHEMA COLLECTION [dbo].[TestRecordingsSchema] AS N'
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">
<xsd:element name="TestRecordings">
<xsd:complexType>
<xsd:complexContent>
<xsd:restriction base="xsd:anyType">
<xsd:sequence>
<xsd:element name="SessionReported" type="xsd:integer" minOccurs="0" />
<xsd:element name="TestRecording" minOccurs="0" maxOccurs="unbounded">
<xsd:complexType>
<xsd:complexContent>
<xsd:restriction base="xsd:anyType">
<xsd:sequence>
<xsd:element name="IsValid" type="xsd:boolean" minOccurs="0" />
<xsd:element name="SessionNo" type="xsd:integer" minOccurs="0" />
<xsd:element name="RecordingData">
<xsd:complexType>
<xsd:complexContent>
<xsd:restriction base="xsd:anyType">
<xsd:all>
<xsd:element name="DeviceCode" type="xsd:string" />
</xsd:all>
</xsd:restriction>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:restriction>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:restriction>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:schema>'

I have some data that looks like this:

<TestRecordings>
<TestRecording>
<IsValid>true</IsValid>
<SessionNumber>1</SessionNumber>
<RecordingData>
<DeviceCode>ABCDE</DeviceCode>
</RecordingData>
</TestRecording>
<TestRecording>
<IsValid>true</IsValid>
<SessionNumber>2</SessionNumber>
<RecordingData>
<DeviceCode>12345</DeviceCode>
</RecordingData>
</TestRecording>
<TestRecording>
<IsValid>true</IsValid>
<SessionNumber>3</SessionNumber>
<RecordingData>
<DeviceCode>67890</DeviceCode>
</RecordingData>
</TestRecording>

</TestRecordings>

I am trying to create a view that selects the DeviceCode from a specific TestRecordings node. How can I indicate the specific node to use in a parameter?

FYI, I can generate a sql statement, to return the specific TestRecoding node, in my vb code-behind that looks like this:

Dim strSQL2 As String = _
"SELECT data.query(' for $b in /TestRecordings/TestRecording[" + selected_node.ToString + "]/RecordingData[1]/DeviceCode return ($b)') as DeviceCode from TestResults where id = @id"

However, I want to do this in view, where I will pass in @selected_node, and would envision the query to look something like this:

SELECT data.query(' for $b in /TestRecordings/TestRecording[@selected_node]/RecordingData[1]/DeviceCode return ($b)') as DeviceCode from TestResults where id = @id

How do I do this in Sql Server 2005?

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-12-20 : 03:46:31

you need to pass it using sql:variable.
something like:-

SELECT data.query(' for $b in /TestRecordings/TestRecording[sql:variable(@selected_node)]/RecordingData[1]/DeviceCode return ($b)') as DeviceCode from TestResults where id = @id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources