Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

using xQuery with XML object in SQL 2005

Posted on 2009-02-23
2
Medium Priority
?
304 Views
Last Modified: 2013-11-11
I am trying to use xQuery in SQL 2005 to return the value of the InvoiceFlag field but I get a blank resultset instead of 1231.
can you tell me where the error is?

thanks

Frank
declare @xmlInputFields xml
set @xmlInputFields = '<Fields count="6">
  <Field name="ApllicationId" /> 
<!--  TemplateType is the prefix of the csv data file 
  --> 
  <Field name="TemplateType" /> 
  <Field name="LetterId" /> 
  <Field name="UserID" /> 
  <Field name="SystemDate" /> 
  <Field name="InvoiceFlag">1231</Field> 
  </Fields>'
 
select  @xmlInputFields.query('
			for $i in /fields/field
			where $i/@name = "InvoiceFlag"
			return ($i/Field)') AS result

Open in new window

0
Comment
Question by:frankytee
2 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 1996 total points
ID: 23720605
I believe it is expecting XML in another format. Check out the format in the links below:

http://www.15seconds.com/Issue/050803.htm
http://msdn.microsoft.com/en-us/library/ms186918.aspx
0
 
LVL 19

Author Closing Comment

by:frankytee
ID: 31550449
sorry for the delay. we had to use openxml method as suggested in your links due to problems with xQuery with SQL.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Create a Windows 10 custom Image with custom task bar and custom start menu using XML for deployment.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…

569 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