Solved

tsql Statement to Select data From an xml file

Posted on 2013-11-22
7
531 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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 article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

822 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