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'".


-- 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">

<value>Respiratory Distress</value>
<value>12-Lead 1</value>
<value xs:nil="true" />



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

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

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

got it figured out. Thanks

This is the syntax I was looking for.

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

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