?
Solved

XML in Sql Server 2005 - Selecting all values in a node?

Posted on 2009-02-18
11
Medium Priority
?
2,489 Views
Last Modified: 2012-06-27
Hi Experts,

I am trying to use SQL servers new XML technology, and I have run into an error already:
Msg 2256, Level 16, State 1, Line 41
XQuery [query()]: Syntax error near '<eof>', expected a "node test".

Any help on this issue and approach to taking XML and putting it into a RMDBs would be appreciated.

Thanks

References:
http://articles.techrepublic.com.com/5100-10878_11-6140404.html#
DECLARE @xml xml
 
SET @xml = '<?xml version="1.0"?>
<callback>
<apiMsgId>996411ad91fa211e7d17bc873aa4a41d</apiMsgId>
<cliMsgId></cliMsgId>
<_timestamp>1218008129</_timestamp>
<_to>279995631564</_to>
<_from>27833001171</_from>
<charge>0.300000</charge>
<status>004</status>
</callback>'
 
/*
DECLARE @Pointer INT 
EXECUTE sp_xml_preparedocument @Pointer OUTPUT,@xml 
 
      SELECT apiMsgId, cliMsgID, _timestamp, _to, _from, charge, status
      OPENXML (@Pointer,'/callback/') 
      WITH 
      ( 
        apiMsgId varchar(200),
		cliMsgID varchar(200),
		_timestamp varchar(200),
		_to varchar(200),
		_from varchar(200),
		charge varchar(200),
		status varchar(200)
      ) 
 
 
 EXEC sp_xml_removedocument @Pointer 
 
*/
 
 
SELECT @xml.query('/callback/')

Open in new window

0
Comment
Question by:Craig Lambie
  • 5
  • 4
10 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 23668291
You don't need the trailing slash on the XPath:

    SELECT @xml.query('/callback')
0
 
LVL 1

Author Comment

by:Craig Lambie
ID: 23668637
Ok, so I was expecting it to look like

apiMsgId,cliMsgID,_timestamp,_to,_from,charge,status
996411ad91fa211e7d17bc873aa4a41d,,1218008129,279995631564,27833001171,0.300000,004

So I can easily insert the values into a Table.... How would I do that?

I was thinking

INSERT INTO tbl (apiMsgId,cliMsgID,_timestamp,_to,_from,charge,status)
SELECT @xml.query('/callback')

Thoughts?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23704904
Changed:
from OPENXML (@Pointer,'/callback')

To:
from OPENXML (@Pointer,'/callback',2)
DECLARE @xml xml
 
SET @xml = '<?xml version="1.0"?>
<callback>
<apiMsgId>996411ad91fa211e7d17bc873aa4a41d</apiMsgId>
<cliMsgId></cliMsgId>
<_timestamp>1218008129</_timestamp>
<_to>279995631564</_to>
<_from>27833001171</_from>
<charge>0.300000</charge>
<status>004</status>
</callback>'
 
 
DECLARE @Pointer INT 
EXECUTE sp_xml_preparedocument @Pointer OUTPUT,@xml 
 
      SELECT apiMsgId, cliMsgID, _timestamp, _to, _from, charge, status
      from OPENXML (@Pointer,'/callback',2) 
      WITH 
      ( 
        apiMsgId varchar(200),
                cliMsgID varchar(200),
                _timestamp varchar(200),
                _to varchar(200),
                _from varchar(200),
                charge varchar(200),
                status varchar(200)
      ) 
 
 
 EXEC sp_xml_removedocument @Pointer 
 
 

Open in new window

0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 1

Author Comment

by:Craig Lambie
ID: 23706687
Brandon,

I hear, in the article I referenced, that using the OPENXML method is very memory intensive.  Is it possible to use the @xml.query method to get the same result?
Also not having to specify the fields, datatypes for the results would be useful.

Thanks
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23706966
Yes it is very memory intensive.  So is xml query though.  I don't know which is worse because I don't have a lot of experience using the xml query method.
0
 
LVL 1

Author Comment

by:Craig Lambie
ID: 23707021
So Brandon, do you think creating an Array in say ASP.net would be better?

I guess, not knowing .query method, this might be hard to answer.
The article (although small error in XML) suggests .query method is much better than OPENXML.

The alternative is to iterate through the XML and put it into an array in the code, then INSERT the array into a table using the SP, maybe a varchar(max) and split(@varchar, ",") type method.

Thoughts?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23713858
Honestly I can't say.  And don't think that you should NEVER use openxml.  Granted, it's not extremely efficient (so it's not like selecting 5 records out of a 10 record table) but it it's not horrible.  
0
 
LVL 1

Author Comment

by:Craig Lambie
ID: 23717055
Ok, fair point.

Given that the answer you gave is correct, not sure if I should ask another question for this, but....

The XML nodes, you will not I put an _ prefixing the fields that are SQL keywords.
How to deal with this error?
I will happily ask another question if you feel is better/ different.

Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'to'.
Msg 319, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

DECLARE @xml xml
 
SET @xml = '<?xml version="1.0"?>
<callback>
<apiMsgId>996411ad91fa211e7d17bc873aa4a41d</apiMsgId>
<cliMsgId></cliMsgId>
<timestamp>1218008129</timestamp>
<to>279995631564</to>
<from>27833001171</from>
<charge>0.300000</charge>
<status>004</status>
</callback>'
 
 
DECLARE @Pointer INT 
EXECUTE sp_xml_preparedocument @Pointer OUTPUT,@xml 
 
      SELECT apiMsgId, cliMsgID, timestamp, to, from, charge, status
      FROM OPENXML (@Pointer,'/callback',2) 
      WITH 
      ( 
        apiMsgId varchar(200),
                cliMsgID varchar(200),
                timestamp varchar(200),
                to varchar(200),
                from varchar(200),
                charge varchar(200),
                status varchar(200)
      ) 
 
 
 EXEC sp_xml_removedocument @Pointer 
 

Open in new window

0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 2000 total points
ID: 23717213
You could do a couple of things.  

The first is that you can place them in brackets, as I have done in the first example below.
The second is that you can use an alias in the openxml statement, and qualify it by placing the XML node name in single quotes after the data dfinition, as I have done in the second example below.
DECLARE @xml xml
 
SET @xml = '<?xml version="1.0"?>
<callback>
<apiMsgId>996411ad91fa211e7d17bc873aa4a41d</apiMsgId>
<cliMsgId></cliMsgId>
<timestamp>1218008129</timestamp>
<to>279995631564</to>
<from>27833001171</from>
<charge>0.300000</charge>
<status>004</status>
</callback>'
 
 
DECLARE @Pointer INT 
EXECUTE sp_xml_preparedocument @Pointer OUTPUT,@xml 
 
      SELECT apiMsgId, cliMsgID, timestamp, [to], [from], charge, status
      FROM OPENXML (@Pointer,'/callback',2) 
      WITH 
      ( 
        apiMsgId varchar(200),
                cliMsgID varchar(200),
                [timestamp] varchar(200),
                [to] varchar(200),
                [from] varchar(200),
                charge varchar(200),
                status varchar(200)
      ) 
 
 
      SELECT apiMsgId, cliMsgID, timestamp, toNum, fromNum, charge, status
      FROM OPENXML (@Pointer,'/callback',2) 
      WITH 
      ( 
        apiMsgId varchar(200),
                cliMsgID varchar(200),
                [timestamp] varchar(200),
                toNum varchar(200) 'to',
                fromNum varchar(200)'from',
                charge varchar(200),
                status varchar(200)
      ) 
 
 
 EXEC sp_xml_removedocument @Pointer 
 

Open in new window

0
 
LVL 1

Author Closing Comment

by:Craig Lambie
ID: 31550401
Tops!! Thanks so much!!
Would love to know how the .query method works... but another day.. This will work for me!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question