I Have SQL data in following Format

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/

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

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/

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

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/

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

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

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/

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/

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

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/

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

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources