Add XML Predicate to SQL Query

[smegal]

Hi, I am working on querying a specific value from my xml file. Below is what I have so far. When running the entire script I get the error "Incorrect syntax near 'varchar'".

Thanks

-- Declarations needed to run sp_xml_preparedocument. This will create XML image.

Declare @idoc int
Declare @doc varchar(max)
Select @doc=

--XML File
'
<?xml version="1.0" encoding="utf-8"?>
<dataset xmlns="http://developer.cognos.com/schemas/xmldata/1/" xmlns:xs="http://www.w3.org/2001/XMLSchema-instance">

<data>
<row>
<value>10137294</value>
<value>Respiratory Distress</value>
<value>2010-09-20T01:35:37</value>
<value>32</value>
<value>Female</value>
<value>White</value>
<value>2010-09-20T01:25:00</value>
<value>2010-09-20T01:35:37</value>
<value>2010-09-20T01:38:14</value>
<value>12-Lead 1</value>
<value>2010-09-20T01:48:53</value>
<value>2010-09-20T02:12:26</value>
<value xs:nil="true" />
</row>

</data>
</dataset>

'

--Executing sp_xml_preparedocument.
exec sp_xml_preparedocument @idoc output, @doc

-- SQL Query with predicate. Attempting to query 1st value form xml file "10137294"
SELECT *
from openxml(@idoc, '/data/row/value',3)
with (value [1] varchar(50))

[iceandrews]

Might want to go to an SQL forum. I can't help you there. Google is your friend.

[smegal]

got it figured out. Thanks

[smegal]

This is the syntax I was looking for.

SELECT *
from openxml(@idoc, '/data/row/value',3)

with
(IncidentNumber varchar(50) 'value[1]',
DrugName varchar(50) 'value[13]'
)