Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

tsql Statement to Select data From an xml file

Posted on 2013-11-22
7
Medium Priority
?
626 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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 to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

618 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