Solved

What is wrong with this query

Posted on 2009-04-07
5
157 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
  • 2
5 Comments
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

759 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now