Read XML document stored in SQL Server with text d

Question

This is an example of the XML being stored in the SQL Server in a table column. Like I have more than 10 columns, here I need to select the 4 columns last inserted

<Section xml:space="aligned" Paste="False" xmlns="http://schemas.microsoft.com">
<Para FontSize="11" FontWeight="Normal" FontStyle="Normal" TextAlignment="Left">
<Run Text="this is the data to be select from here" />
</Para>
</Section>

this the table stracture

ID       Name         XMLContent                                  CreatedDate                   |       Modified



--------|------------|------------------|------------------------|-----------------------------------------
1       | CATEGORYID |<Section xml:space="aligned" Paste="False".|    |2011-04-05 12:28:15.030  |      
2       |    114     |<Section xml:space="aligned" Paste="False".|2011-04-05 12:28:15.030       |  

should get the result

1   |this is the data to be select from here| 
2   |really a nice solution, |

i had saw the previous blogs i had used but this is for xml data type

SELECT TOP 5
    ID,
    XmlContent.value('(/Section/Para/Data/@Text)[1]', 'varchar(200)')
FROM dbo.YourTableName
ORDER BY CreatedDate DESC

but i need for xml data stored in text datatype please suggest thanks

previous blog data getiing Null

DECLARE @xmltbl TABLE (ID INT, XmlData XML)

INSERT INTO @xmltbl(ID, XmlData) 
VALUES(1, '<Section xml:space="preserve" HasTrailingParagraphBreakOnPaste="False" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation">
<Paragraph FontSize="11" FontFamily="Portable User Interface" Foreground="#FF000000" FontWeight="Normal" FontStyle="Normal" FontStretch="Normal" TextAlignment="Left">
<Run Text="Jubilee Financial Products is one of Europe’s largest structured product providers, working with 30 of the worlds’ largest banks. Jubilee is regulated in Ireland under MIFID and all products are regulated." />
</Paragraph></Section>'), 
(2, '<Section xml:space="preserve" HasTrailingParagraphBreakOnPaste="False" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation">
<Paragraph FontSize="11" FontFamily="Portable User Interface" Foreground="#FF000000" FontWeight="Normal" FontStyle="Normal" FontStretch="Normal" TextAlignment="Left">
<Run Text="Jubilee Financial Products is one of Europe’s largest structured product providers, working with 30 of the worlds’ largest banks. Jubilee is regulated in Ireland under MIFID and all products are regulated." />
</Paragraph></Section> ');

;WITH XMLNAMESPACES(DEFAULT 'xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation')
SELECT 
    ID,
    DataText = XmlData.value('(/Section/Paragraph/Run/@Text)[1]', 'varchar(200)')
FROM @xmltbl 

Answer 1

The best way would to be convert your column to datatype XML since TEXT is dead and should not be used anymore:

ALTER TABLE dbo.YourTableName
   ALTER COLUMN XmlContent XML 

If you cannot do this, then you'll need to cast the TEXT to XML everytime you query:

SELECT 
    CAST(XmlContent AS XML).value('declare namespace ns="http://schemas.microsoft.com";(/ns:Section/ns:Para/ns:Run/@Text)[1]', 'varchar(200)')
FROM dbo.YourTableHere