阅读存储在SQL Server文本数据类型的XML文档？(Read XML document sto

这是被存储在一个表列中的SQL Server中的XML的示例。像我有10多列，在这里我需要选择4列最后插入

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

此表意象结构

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       |

应该得到的结果

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

我已经看到了以前的博客，我用了，但是这是对XML数据类型

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

但我需要存储在文本数据类型的XML数据请建议谢谢

以前的博客数据getiing空

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

最好的办法是你列转换为数据类型的XML ，因为TEXT是死的，不应该再使用：

ALTER TABLE dbo.YourTableName
   ALTER COLUMN XmlContent XML

如果你不能做到这一点，那么你就需要转换TEXT到XML你每次查询：

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