Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 I Have SQL data in following Format

Author  Topic 

yomi
Starting Member

25 Posts

Posted - 2013-01-31 : 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

52326 Posts

Posted - 2013-01-31 : 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/

Go to Top of Page

yomi
Starting Member

25 Posts

Posted - 2013-01-31 : 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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-31 : 04:58:59
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

25 Posts

Posted - 2013-01-31 : 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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-31 : 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/

Go to Top of Page

yomi
Starting Member

25 Posts

Posted - 2013-02-07 : 06:24:09
What is this date format 2013-01-09T11:54:49:732.187

Yogesh
Go to Top of Page

yomi
Starting Member

25 Posts

Posted - 2013-02-07 : 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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-07 : 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/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-07 : 06:29:40
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

25 Posts

Posted - 2013-02-13 : 02:48:36
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

52326 Posts

Posted - 2013-02-13 : 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/

Go to Top of Page

yomi
Starting Member

25 Posts

Posted - 2013-02-18 : 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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-18 : 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/

Go to Top of Page
   

- Advertisement -