Solved

tsql Statement to Select data From an xml file

Posted on 2013-11-22
7
513 Views
Last Modified: 2013-11-27
I have an xml file which needs to be read by a sql statement.  
The result should be a list of values from the xml file.

Here is the xml file:

<?xml version="1.0" encoding="utf-8" ?>
<users>
          <user><FirstName>Suresh</FirstName>
                   <LastName>Dasari</LastName>
                   <UserName>SureshDasari</UserName>
                   <Job>Team Leader</Job>
          </user>
          <user><FirstName>Mahesh</FirstName>
                   <LastName>Dasari</LastName>
                   <UserName>MaheshDasari</UserName>
                   <Job>Software Developer</Job>
          </user>
          <user><FirstName>Madhav</FirstName>
                <LastName>Yemineni</LastName>
                <UserName>MadhavYemineni</UserName>
                <Job>Business Analyst</Job>
          </user>
</users>
---

I am trying the following tsql.

ALTER PROCEDURE [dbo].[prc_readxmldata]

(

@XMLdata XML

)


AS

BEGIN

SET @XMLdata =
'C:\Users\Dovberman\documents\visual studio 2012\Projects\StockProNew1\StockProNew1\Sample.xml'

SELECT t.value('(FirstName/text())[1]','nvarchar(120)')AS FirstName,

t.value('(LastName/text())[1]','nvarchar(120)')AS LastName,

t.value('(UserName/text())[1]','nvarchar(120)')AS UserName,

t.value('(Job/text())[1]','nvarchar(120)')AS Job

FROM

@XMLdata.nodes('/users/user')AS TempTable(t)


END

The stored proc compiles even though the t.value error is cannot find column t

The procedure executes and displays the header row,but does not display any data.

The return value is 0

How can I avoid the t.value error?

Thanks,
0
Comment
Question by:Dovberman
  • 4
  • 2
7 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 39668641
you have tried  changing
this
FROM

@XMLdata.nodes('/users/user')AS TempTable(t)


to this ?
FROM

@XMLdata.nodes('/users/user')AS t
0
 

Author Comment

by:Dovberman
ID: 39668711
Yes,

This caused a compile error;

Msg 318, Level 15, State 0, Procedure prc_readxmldata, Line 34
The table (and its columns) returned by a table-valued method need to be aliased.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39669045
You have a procedure with an XML parameter, which then you assign inside the procedure with a string that actually is the path to the file. That doesn't make sense.

First you have to import that XML somehow inside sql and then you can deal with the xml parsing using the xml types. Here is a link that can be helpful:

http://www.mssqltips.com/sqlservertip/2899/importing-and-processing-data-from-xml-files-into-sql-server-tables/
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:Dovberman
ID: 39669194
Thanks, I will try this.

I thought I could use the following process as described in:

http://www.aspdotnet-suresh.com/2012/12/aspnet-send-xml-file-as-parameter-to.html
0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 39669199
That is different. The xml file content is read and passes to the stored procedure as XML in the VB code and not in the SQL code.
0
 

Author Comment

by:Dovberman
ID: 39669266
Thanks for explaining.
0
 

Author Closing Comment

by:Dovberman
ID: 39680532
Thank you
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

863 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

23 Experts available now in Live!

Get 1:1 Help Now