How to Split data using xpath or XQuery or OpenXML

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

How to Split data using xpath or XQuery or OpenXML

Author

Topic

naveengopinathasari
Yak Posting Veteran

60 Posts

Posted - 2011-06-30 : 02:50:58

I want to split data as show below

Create table Test (Splitdata varchar(100))

INSERT INTO Test (Splitdata ) values ('A B C')
INSERT INTO Test (Splitdata ) values ('1 2 3')
INSERT INTO Test (Splitdata ) values ('AAAA BBBB CCCC')
INSERT INTO Test (Splitdata ) values ('ZZZZ YYYY XXX')
INSERT INTO Test (Splitdata ) values ('111 222 333')

I want data to be displayed in 3 columns a
FirstSplit SecondSplit ThirdSplit
A B C
1 2 3
AAAA BBBB CCCC
ZZZZ YYYY XXX
111 222 333

Using only xPath or xQuery or OpenXML

Lets unLearn

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-06-30 : 03:43:03

No need for extravaganzas.

SELECT PARSENAME(theData, 3) AS FirstSplit,
PARSENAME(theData, 2) AS SecondSplit,
PARSENAME(theData, 1) AS ThirdSplit
FROM (SELECT REPLACE(SplitData, ' ', '.') AS theData FROM dbo.Test) AS f

N 56°04'39.26"
E 12°55'05.63"

naveengopinathasari
Yak Posting Veteran

60 Posts

Posted - 2011-07-01 : 07:27:06

quote:
Originally posted by Peso

No need for extravaganzas.

SELECT PARSENAME(theData, 3) AS FirstSplit,
PARSENAME(theData, 2) AS SecondSplit,
PARSENAME(theData, 1) AS ThirdSplit
FROM (SELECT REPLACE(SplitData, ' ', '.') AS theData FROM dbo.Test) AS f

N 56°04'39.26"
E 12°55'05.63"

Thanks for the solution. its sounds good.

but is it possible to do with xpath and xquery???

Lets unLearn

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-01 : 07:43:47

Yes. Same technique but replace space with node delimiters.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources