?
Solved

tsql Statement to Select data From an xml file

Posted on 2013-11-22
7
Medium Priority
?
594 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
[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
  • 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 27

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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 

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 27

Accepted Solution

by:
Zberteoc earned 2000 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

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

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…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

752 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