Splitting XML - 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

Splitting XML

Author

Topic

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2011-09-29 : 03:50:35

Hi All,

When I ran my SP in server i will get the below xml :

<ReturnMessage id="3654397910">
<AdC ocean="AORWGL">4815043</AdC>
<MessageStatus code="100" time="2011-09-25 22:02:55">status ok</MessageStatus>
<MessageData>700A200000160039C943C00000</MessageData>
<Flags les="0" app="0" />
</ReturnMessage>

Now, I want the split the <MessageData> element value 700A200000160039C943C00000 as 7-00-A2-00000160-039c943c-00000.

Kindly help me in this!! Its very urgent...
Thanks

rams

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-09-29 : 03:55:08

SELECT SUBSTRING(Original, 1, 1) + '-' + SUBSTRING(Original, 2, 2) + '-' + SUBSTRING(Original, 4, 2) + '-' + SUBSTRING(Original, 6, 8) + '-' + SUBSTRING(Original, 14, 8) + '-' + SUBSTRING(Original, 22, 5)
FROM (
SELECT @XML.value('/ReturnMessage/MessageData/text()[1]', 'VARCHAR(100)') AS Original
) AS d

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

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2011-09-29 : 04:07:57

Hi,

First of all thanks for ur immediate reply :

when i ran the query that you have given me in the following way, its giving me the following error :

declare @xml xml
set @xml = '<ReturnMessage id="3654397910">
<AdC ocean="AORWGL">4815043</AdC>
<MessageStatus code="100" time="2011-09-25 22:02:55">status ok</MessageStatus>
<MessageData>700A200000160039C943C00000</MessageData>
<Flags les="0" app="0" />
</ReturnMessage>'
SELECT SUBSTRING(Original, 1, 1) + '-' + SUBSTRING(Original, 2, 2) + '-' + SUBSTRING(Original, 4, 2) + '-' + SUBSTRING(Original, 6, 8) + '-' + SUBSTRING(Original, 14, 8) + '-' + SUBSTRING(Original, 22, 5)
FROM (
SELECT @XML.value('/ReturnMessage/MessageData/text()[1]', 'VARCHAR(100)') AS Original
) AS d

Error : Msg 2389, Level 16, State 1, Line 10
XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

kindly help me on this

rams

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2011-09-29 : 04:25:42

Hi Peso,

Iam getting the following error :

Msg 2389, Level 16, State 1, Line 10
XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

Kindly help me on the same.

rams

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-29 : 05:04:56

Try this:

DECLARE @XML xml
SET @XML =N'
<ReturnMessage id="3655041356">
<AdC ocean="PACCGL">4815044</AdC>
<MessageStatus code="100" time="2011-09-26 01:43:20">status ok</MessageStatus>
<MessageData>700A20000018C0375494400000</MessageData>
<Flags les="0" app="0" />
</ReturnMessage>'

SELECT
SUBSTRING(MessageData, 1, 1) + '-' +
SUBSTRING(MessageData, 2, 2) + '-' +
SUBSTRING(MessageData, 4, 2) + '-' +
SUBSTRING(MessageData, 6, 8) + '-' +
SUBSTRING(MessageData, 14, 8) + '-' +
SUBSTRING(MessageData, 22, 5)
FROM
(
SELECT
msg.x.value('MessageData[1]', 'varchar(100)') AS MessageData
FROM @XML.nodes('//ReturnMessage') msg(x)
) AS tmp

Best Regards
Vadivel

http://vadivel.blogspot.com

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2011-09-29 : 05:20:44

Thanks a lot vadivel, you are really life saver for me :) Also could you please let me know how to convert the hexadecimal value to decimal in sql

rams

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-29 : 05:49:52

You are welcome :)

Best Regards
Vadivel

http://vadivel.blogspot.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-09-29 : 06:02:14

Why would you want "//" in the xml? There are no other levels...

DECLARE @Data XML

SET @Data = '<ReturnMessage id="3654397910">
<AdC ocean="AORWGL">4815043</AdC>
<MessageStatus code="100" time="2011-09-25 22:02:55">status ok</MessageStatus>
<MessageData>700A200000160039C943C00000</MessageData>
<Flags les="0" app="0" />
</ReturnMessage>'

SELECT	SUBSTRING(Original, 1, 1)
	+ '-' + SUBSTRING(Original, 2, 2)
	+ '-' + SUBSTRING(Original, 4, 2)
	+ '-' + SUBSTRING(Original, 6, 8)
	+ '-' + SUBSTRING(Original, 14, 8)
	+ '-' + SUBSTRING(Original, 22, 5)
FROM	(
		SELECT @Data.value('(/ReturnMessage/MessageData[1]/text())[1]', 'VARCHAR(100)') AS Original
	) AS d

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-09-29 : 06:07:05

DECLARE @Src VARCHAR(8) = 'ffff'

SELECT CAST(CONVERT(VARBINARY(8), @Src, 2) AS BIGINT)

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

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2011-09-29 : 06:10:02

Thanks Peso :)

rams

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources