| Author |
 Topic  |
|
|
yomi
Starting Member
India
12 Posts |
 Posted - 01/31/2013 : 03:50:23
|
<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
47023 Posts |
Posted - 01/31/2013 : 04:12:05
|
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/
|
 |
|
|
yomi
Starting Member
India
12 Posts |
Posted - 01/31/2013 : 04:31:43
|
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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 01/31/2013 : 04:58:59
|
what do you mean by data in table format? illustrate with sample data?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
yomi
Starting Member
India
12 Posts |
Posted - 01/31/2013 : 05:11:33
|
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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 01/31/2013 : 09:00:56
|
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/
|
|
|
|
yomi
Starting Member
India
12 Posts |
Posted - 02/07/2013 : 06:24:09
|
What is this date format 2013-01-09T11:54:49:732.187
Yogesh |
|
|
|
yomi
Starting Member
India
12 Posts |
Posted - 02/07/2013 : 06:26:20
|
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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 02/07/2013 : 06:28:24
|
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/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
|
|
yomi
Starting Member
India
12 Posts |
Posted - 02/13/2013 : 02:48:36
|
Is Your query will work on this Databases
1. SQL
2. MYSEQl
3. ORACLE
4. PostGrace
Yogesh |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 02/13/2013 : 04:43:13
|
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/
|
|
|
|
yomi
Starting Member
India
12 Posts |
Posted - 02/18/2013 : 05:29:05
|
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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 02/18/2013 : 05:38:07
|
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/
|
|
|
| |
Topic |
|
I Have SQL data in following Format
