0

How to retrieve xml type data from non xml type column using SQL I am having one table and a column inside it xml is a column of type ntext

xml column data example is given below

<message to="4075@abc.myftp.org" type="chat" from="5082@abc.myftp.org/e76bea0f">
<body>james bond</body>
<active xmlns="http://jabber.org/protocol/chatstates" />
</message>

I want to fetch "to","from" attribute value and also tag value

Please suggest

Pankaj Das
  • 45
  • 1
  • 7

1 Answers1

0

You've missed the closing tag </message> in the xml, but if you add it, you can use value() function:

declare @temp table (data ntext)

insert into @temp (data)
select '<message to="4075@abc.myftp.org" type="chat" from="5082@abc.myftp.org/e76bea0f">
<body>james bond</body>
<active xmlns="http://jabber.org/protocol/chatstates" />
</message>'

select
    c.data.value('message[1]/@to', 'nvarchar(max)')
from @temp as t
    outer apply (select cast(data as xml)) as c(data)
Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
  • Thank you @Roman for the perfect answer and also for quick response – Pankaj Das Mar 20 '15 at 09:12
  • Hi Roman, i am trying to run this query from PHP application, but getting error /***** [nativecode=1934 - SELECT failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.] *****/ Can you please suggest – Pankaj Das Mar 20 '15 at 09:36