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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 664
  • Last Modified:

tsql Statement to Select data From an xml file

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
Dovberman
Asked:
Dovberman
  • 4
  • 2
1 Solution
 
LowfatspreadCommented:
you have tried  changing
this
FROM

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


to this ?
FROM

@XMLdata.nodes('/users/user')AS t
0
 
DovbermanAuthor Commented:
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
 
ZberteocCommented:
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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
DovbermanAuthor Commented:
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
 
ZberteocCommented:
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
 
DovbermanAuthor Commented:
Thanks for explaining.
0
 
DovbermanAuthor Commented:
Thank you
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.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now