SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 I Have SQL data in following Format
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

yomi
Starting Member

India
25 Posts

Posted - 01/31/2013 :  03:50:23  Show Profile  Reply with Quote
<Employees iid="2" EmployeeName="yogesh" Age="1" Department="sm" Gender="Male" EmployeeNo="55" version="2013-01-09T11:54:49:732.187">

I want to write SQL query to retrieve data in Table format and column format

Yogesh

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/31/2013 :  04:12:05  Show Profile  Reply with Quote
your xml is not wellformed. also date format is not one of standard allowable pattern.

if both are fixed like below, you can use attached solution

declare @x xml
set @x='<Employees iid="2" EmployeeName="yogesh" Age="1" Department="sm" Gender="Male" EmployeeNo="55" version="2013-01-09T11:54:49.732"/>'

select
t.u.value('./@iid[1]','int') as iid,
 t.u.value('./@EmployeeName[1]','varchar(50)') as EmployeeName,
t.u.value('./@Age[1]','int') as Age,
t.u.value('./@Department[1]','varchar(10)') as Department,
t.u.value('./@Gender[1]','varchar(6)') as Gender,
t.u.value('./@EmployeeNo[1]','int') as EmployeeNo,
t.u.value('./@version[1]','datetime2') as version
from @x.nodes('/Employees') t(u)


output
------------------------------------------------------------------------------------------------------
iid	EmployeeName	Age	Department	Gender	EmployeeNo	version
------------------------------------------------------------------------------------------------------
2	yogesh	        1	sm	        Male	55	        2013-01-09 11:54:49.7320000



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

yomi
Starting Member

India
25 Posts

Posted - 01/31/2013 :  04:31:43  Show Profile  Reply with Quote
Hi visakh16,
i don't have single row like this
<Employees iid="2" EmployeeName="yogesh" Age="1" Department="sm" Gender="Male" EmployeeNo="55" version="2013-01-09T11:54:49.732"/>
i have same data in a table format then what is solution.

Yogesh
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/31/2013 :  04:58:59  Show Profile  Reply with Quote
what do you mean by data in table format? illustrate with sample data?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

yomi
Starting Member

India
25 Posts

Posted - 01/31/2013 :  05:11:33  Show Profile  Reply with Quote
I have a table in SQL with XML Format data like
<Employees iid="2" EmployeeName="yogesh" Age="1" Department="sm" Gender="Male" EmployeeNo="55" version="2013-01-09T11:54:49.732"/>
I want this in table format what you show in output.

Yogesh
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/31/2013 :  09:00:56  Show Profile  Reply with Quote
its similar to what i gave before


declare @test table
(id int,
x xml
)
insert @test
select 1,'<Employees iid="2" EmployeeName="yogesh" Age="1" Department="sm" Gender="Male" EmployeeNo="55" version="2013-01-09T11:54:49.732"/>'

select
t.u.value('./@iid[1]','int') as iid,
 t.u.value('./@EmployeeName[1]','varchar(50)') as EmployeeName,
t.u.value('./@Age[1]','int') as Age,
t.u.value('./@Department[1]','varchar(10)') as Department,
t.u.value('./@Gender[1]','varchar(6)') as Gender,
t.u.value('./@EmployeeNo[1]','int') as EmployeeNo,
t.u.value('./@version[1]','datetime2') as version
from @test
cross apply x.nodes('/Employees') t(u)



output
----------------------------------------------------------------------------------
iid	EmployeeName	Age	Department	Gender	EmployeeNo	version
-----------------------------------------------------------------------------------
2	yogesh	        1	sm	        Male	55	        2013-01-09 11:54:49.7320000



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

yomi
Starting Member

India
25 Posts

Posted - 02/07/2013 :  06:24:09  Show Profile  Reply with Quote
What is this date format 2013-01-09T11:54:49:732.187

Yogesh
Go to Top of Page

yomi
Starting Member

India
25 Posts

Posted - 02/07/2013 :  06:26:20  Show Profile  Reply with Quote
Which type of query is this
declare @test table
(id int,
x xml
)
insert @test
select 1,'<Employees iid="2" EmployeeName="yogesh" Age="1" Department="sm" Gender="Male" EmployeeNo="55" version="2013-01-09T11:54:49.732"/>'

select
t.u.value('./@iid[1]','int') as iid,
t.u.value('./@EmployeeName[1]','varchar(50)') as EmployeeName,
t.u.value('./@Age[1]','int') as Age,
t.u.value('./@Department[1]','varchar(10)') as Department,
t.u.value('./@Gender[1]','varchar(6)') as Gender,
t.u.value('./@EmployeeNo[1]','int') as EmployeeNo,
t.u.value('./@version[1]','datetime2') as version
from @test
cross apply x.nodes('/Employees') t(u)

Yogesh
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/07/2013 :  06:28:24  Show Profile  Reply with Quote
quote:
Originally posted by yomi

Which type of query is this
declare @test table
(id int,
x xml
)
insert @test
select 1,'<Employees iid="2" EmployeeName="yogesh" Age="1" Department="sm" Gender="Male" EmployeeNo="55" version="2013-01-09T11:54:49.732"/>'

select
t.u.value('./@iid[1]','int') as iid,
t.u.value('./@EmployeeName[1]','varchar(50)') as EmployeeName,
t.u.value('./@Age[1]','int') as Age,
t.u.value('./@Department[1]','varchar(10)') as Department,
t.u.value('./@Gender[1]','varchar(6)') as Gender,
t.u.value('./@EmployeeNo[1]','int') as EmployeeNo,
t.u.value('./@version[1]','datetime2') as version
from @test
cross apply x.nodes('/Employees') t(u)

Yogesh


Transact sql using xquery functions for xml

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/07/2013 :  06:29:40  Show Profile  Reply with Quote
quote:
Originally posted by yomi

What is this date format 2013-01-09T11:54:49:732.187

Yogesh


thats the XML datetime data type

see

http://www.w3schools.com/schema/schema_dtypes_date.asp

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

yomi
Starting Member

India
25 Posts

Posted - 02/13/2013 :  02:48:36  Show Profile  Reply with Quote
Is Your query will work on this Databases
1. SQL
2. MYSEQl
3. ORACLE
4. PostGrace

Yogesh
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/13/2013 :  04:43:13  Show Profile  Reply with Quote
It is T-SQL (Transact SQL) which is MS SQL Servers implementation of SQL. Also this is a MS SQL Server forum

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

yomi
Starting Member

India
25 Posts

Posted - 02/18/2013 :  05:29:05  Show Profile  Reply with Quote
Is it possible to assign/set xquery data to variables if yes then please respond ASAP

declare @test table
(id int,
x xml
)
insert @test
select 1,'<Employees iid="2" EmployeeName="yogesh" Age="1" Department="sm" Gender="Male" EmployeeNo="55" version="2013-01-09T11:54:49.732"/>'

select
t.u.value('./@iid[1]','int') as iid,
t.u.value('./@EmployeeName[1]','varchar(50)') as EmployeeName,
t.u.value('./@Age[1]','int') as Age,
t.u.value('./@Department[1]','varchar(10)') as Department,
t.u.value('./@Gender[1]','varchar(6)') as Gender,
t.u.value('./@EmployeeNo[1]','int') as EmployeeNo,
t.u.value('./@version[1]','datetime2') as version
from @test
cross apply x.nodes('/Employees') t(u)



Yogesh
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/18/2013 :  05:38:07  Show Profile  Reply with Quote
Didnt understand your question. do you mean shredding data from XML and storing in variables?
That will make sense only if you've single node returned by your xquery. Otherwise you'll have a full resultset (multiple rows) which cant be stored in a variable.



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 1.41 seconds. Powered By: Snitz Forums 2000