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"?>
10137294Respiratory Distress2010-09-20T01:35:3732FemaleWhite2010-09-20T01:25:002010-09-20T01:35:372010-09-20T01:38:1412-Lead 12010-09-20T01:48:532010-09-20T02:12:26

'

--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]'
)