Working with XML datatype - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Working with XML datatype

Author

Topic

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2009-01-23 : 08:55:09

create table test(id int,name varchar(100))
create table testXML(id int ,data xml)

select * from test FOR XML path

insert into test values(1,'aaa')
insert into test values(2,'bbb')
insert into test values(3,'ccc')
insert into test values(4,'ddd')

select * from test for XML path

insert into testxml values
(1,'<row><id>1</id><name>aaa</name></row><row><id>2</id><name>bbb</name></row><row><id>3</id><name>ccc</name></row><row><id>4</id><name>ddd</name></row>')

select data.query('data(//name)') ,data.query('data(//id)')
from testxml

the out put is "aaa bbb ccc ddd" "1 2 3 4"
can i split this output in 4 sperate rows.

Thanks

========================================
Project Manager who loves to code.
===============
Ask to your self before u ask someone

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-23 : 09:49:22

something like:-


select data.query('for $i in //name return data($i)') ,data.query('for $i in //id return data($i)')
from testxml

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-23 : 10:02:07

Or you can use

value()

method also.

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2009-01-23 : 10:43:03

Hi Visakh16 i still get the values concatnited and i tried using value() it takes [n]..what shd the n be...

========================================
Project Manager who loves to code.
===============
Ask to your self before u ask someone

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-23 : 11:01:39

What did you try with value()?

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2009-01-23 : 11:30:26

i tied 0,1,10,20 i get to see only one char...

========================================
Project Manager who loves to code.
===============
Ask to your self before u ask someone

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-23 : 12:32:26

try this also:-


SELECT r.s.value('text()[1]','varchar(50)'),r.s.value('../id[1]','int')
FROM testxml t
CROSS APPLY data.nodes('//name')r(s)

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2009-01-24 : 07:04:38

that perfect Visakh16 .. thanks buddy.

========================================
Project Manager who loves to code.
===============
Ask to your self before u ask someone

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2009-01-24 : 08:06:38

Hi Visakh16

i need a small clarification

i have a table which has 34k rows and i apply the same approach it takes almost 5 min to process. this will not help me. any way i can improve the performance?

========================================
Project Manager who loves to code.
===============
Ask to your self before u ask someone

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-24 : 08:36:01

try an xml index.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources