Error in 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

Error in Splitting XML!!

Author

Topic

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2011-09-29 : 04:52:22

Hi All

Iam getting the following error while trying to split the xml :

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(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 9
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:01:58

If you want to retrieve MessageData then 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
msg.x.value('MessageData[1]', 'varchar(100)') AS MessageData
FROM @XML.nodes('//ReturnMessage') msg(x)

Best Regards
Vadivel

http://vadivel.blogspot.com

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-29 : 05:09:02

This seems to be a duplicate of this - http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=166059

Best Regards
Vadivel

http://vadivel.blogspot.com

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2011-09-29 : 05:15:42

thanks a lot , also could you please tell me how to convert the hexadecimal values to decimal in sql

rams

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-29 : 05:48:34

In SQL Server 2008 we can convert Hexadecimal value to INT using CONVERT function itself directly. For earlier versions of SQL Server may be you need to think of CLR based solution for this.

Best Regards
Vadivel

http://vadivel.blogspot.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

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

DECLARE @Src VARCHAR(8) = 'ffff'

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

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

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-29 : 06:19:28

Swepeso,

Is your query supposed to work with SQL Server 2005 as well? I am assuming it to be compatible with SQL Server 2008 or above.

But yeah we could use Varbinary which didn't strike me earlier :) I tried the below queries in SQL Server 2005 and it worked.

DECLARE @a VARCHAR(100)
SEt @a = '700A20000018C0375494400000'
SELECT CAST(CONVERT(VARBINARY, @a) AS BIGINT)

DECLARE @a VARBINARY(10)
SET @a=0x070500009F4F0C00
SELECT CAST(@a AS DECIMAL(18,2))

Best Regards
Vadivel

http://vadivel.blogspot.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources