Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

What is wrong with this query

Posted on 2009-04-07
5
Medium Priority
?
164 Views
Last Modified: 2012-05-06
Should this query returns
99-32G56, Vaccine, TRIM, VAC
?

It returns no row.
Pax

DECLARE @x xml; 
SET @x = N'<book>
		<book_number>99-32G56</book_number>
		<book_title>Vaccine</book_title>
		<abbreviated_title>VAC</abbreviated_title>
		<institute>TRIM</institute>
		<coordinator>
			<name>May Flower</name>
			<phone>3014513267</phone>
		</coordinator>
		<address>Building 45
Room 11C23
10 Center Drive
Middle Row, MD 20000</address>
		<start_date>2002-04-17</start_date>
		<end_date>2001-11-20</end_date>
</book>';
		
		select 
		c.value(N'(book/book_number)[1]', N'nvarchar(20)') AS bookID ,
		c.value(N'(book/book_title)[1]', N'nvarchar(100)') AS title, 
		c.value(N'(book/institute)[1]', N'nvarchar(100)') AS ins,
		c.value(N'(book/abbreviated_title)[1]', N'nvarchar(100)') AS abb_title

Open in new window

0
Comment
Question by:cpeters5
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 Comments
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 2000 total points
ID: 24090845
You don't have a c.value, you have @x.value
DECLARE @x xml; 
SET @x = N'<book>
                <book_number>99-32G56</book_number>
                <book_title>Vaccine</book_title>
                <abbreviated_title>VAC</abbreviated_title>
                <institute>TRIM</institute>
                <coordinator>
                        <name>May Flower</name>
                        <phone>3014513267</phone>
                </coordinator>
                <address>Building 45
Room 11C23
10 Center Drive
Middle Row, MD 20000</address>
                <start_date>2002-04-17</start_date>
                <end_date>2001-11-20</end_date>
</book>';
                
                select 
                @x.value(N'(book/book_number)[1]', N'nvarchar(20)') AS bookID ,
                @x.value(N'(book/book_title)[1]', N'nvarchar(100)') AS title, 
                @x.value(N'(book/institute)[1]', N'nvarchar(100)') AS ins,
                @x.value(N'(book/abbreviated_title)[1]', N'nvarchar(100)') AS abb_title

Open in new window

0
 

Author Closing Comment

by:cpeters5
ID: 31567697
Thanks Brandon.  I will be back with more questions.  This is just the first trial of my attempt to load large xml file into databases.  I am still struggling...
pax
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24090922
May I ask why a accurate, functional answer on my FIRST post deserves a "B" grade?
0
 
LVL 1

Expert Comment

by:modus_operandi
ID: 24142877
cpeters5,
 
BrandonGalderisi has asked (http://www.experts-exchange.com/Q_24321321.html) for an explanation of the grade you awarded for this question. Please read http://www.experts-exchange.com/help.jsp?hi=403 and then respond to the request for an explanation of the grade given.
 
As indicated in that help topic, a typical grade here is an A.  If the Expert(s) provided you a good starting point but you still had to add a significant amount of work or research to find the eventual solution, then a B is common, but when you use the B grade you should post an explanation as to why you awarded a B, including what you had to do on your own to get to the solution.
 
A C grade is usually reserved only for those cases where the Expert(s) gave vague and/or incomplete responses, and then did not reply to reasonable requests for further information.  Before you award a C, you should *always* post a comment in your question clearly stating the gaps in the current responses, and then allow a reasonable amount of time for the Expert(s) to address those gaps.  Indeed, many Experts would prefer having a question deleted altogether than to have a question closed with a C.
 
Please review this message and the help topic linked above.  If after doing so you want to change the grade then post a comment to this question to that effect and a Moderator will help you.
 
Thank you,
 
modus_operandi
EE Moderator
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

705 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