OPENXML does not work - SQL Server Forums

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)

OPENXML does not work

Author

Topic

byang
Starting Member

7 Posts

Posted - 2007-10-30 : 16:21:49

I pasted the following sample OPENXML code(obtained from SQL Server 2005 help) as a new query, but it gave me an syntacs error,
Msg 102, Level 15, State 1, Line 22
Incorrect syntax near '('.
Is this a SQL Server 2005 bug or my SQL Server didn't setup properly?

byang

--code start here
DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
<OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
<OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
<OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2007-10-30 : 16:24:40

Works for me.

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

byang
Starting Member

7 Posts

Posted - 2007-10-30 : 16:38:16

If I delete the WITH clause, the script can be parsed, but when I run, I get another error,

Msg 208, Level 16, State 1, Line 20
Invalid object name 'OPENXML'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-30 : 17:09:05

Which compatibility mode are you using on your database?

E 12°55'05.25"
N 56°04'39.16"

byang
Starting Member

7 Posts

Posted - 2007-10-31 : 10:52:20

The compatibility level of the database is 70.

quote:
Originally posted by Peso

Which compatibility mode are you using on your database?
E 12°55'05.25"
N 56°04'39.16"

byang
Starting Member

7 Posts

Posted - 2007-10-31 : 11:00:14

quote:
Originally posted by Peso

Which compatibility mode are you using on your database?

This is the problem, when I switch to master database, it works.

Thank you!

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-31 : 16:07:06

You're welcome.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources