Error while splitting xml!!

Question

Post reply

Error while splitting xml!!

rams.prsk

Ten Centuries

Points: 1241
More actions
September 29, 2011 at 2:53 am

#248659

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

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

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

rams.prsk Ten Centuries Points: 1241 More actions · Answer 2

Thanks a lot, it worked for me and saved my time. 🙂