在sql server中解析xml

Question

我有一个包含xtext的ntext类型列的表。我试图应用很多示例来说明如何从特定节点的xml中为公司名称获取值，但仍然会出现语法错误。下面是我做了什么，除了取代我的select语句的实际XML输出

DECLARE @companyxml xml 

    SET @companyxml = 
    '<Home> 
     <slideshowImage1>1105</slideshowImage1> 
     <slideshowImage2>1106</slideshowImage2> 
     <slideshowImage3>1107</slideshowImage3> 
     <slideshowImage4>1108</slideshowImage4> 
     <slideshowImage5>1109</slideshowImage5> 
     <bottomNavImg1>1155</bottomNavImg1> 
     <bottomNavImg2>1156</bottomNavImg2> 
     <bottomNavImg3>1157</bottomNavImg3> 
     <pageTitle>Acme Capital Management |Homepage</pageTitle> 
     <metaKeywords><![CDATA[]]></metaKeywords> 
     <metaDescription><![CDATA[]]></metaDescription> 
     <companyName>Acme Capital Management</companyName> 
     <logoImg>1110</logoImg> 
     <pageHeader></pageHeader> 
    </Home>' 

SELECT c.value ('companyName','varchar(1000)') AS companyname 
FROM @companyxml.nodes('/Home') AS c 

出于某种原因，选择c.value语句有语法问题，我想不通。在悬停在SSMS中时，它表示'找不到列'c'或用户定义的函数或聚合“c.value”，或名称不明确。

任何有关语法的帮助将不胜感激。

Answer 1

在身为菜鸟的危险尝试这种

DECLARE @companyxml xml 

SET @companyxml = 
'<Home> 
    <slideshowImage1>1105</slideshowImage1> 
    <slideshowImage2>1106</slideshowImage2> 
    <slideshowImage3>1107</slideshowImage3> 
    <slideshowImage4>1108</slideshowImage4> 
    <slideshowImage5>1109</slideshowImage5> 
    <bottomNavImg1>1155</bottomNavImg1> 
    <bottomNavImg2>1156</bottomNavImg2> 
    <bottomNavImg3>1157</bottomNavImg3> 
    <pageTitle>Acme Capital Management Homepage</pageTitle> 
    <metaKeywords>CDATA</metaKeywords> 
    <metaDescription>CDATA</metaDescription> 
    <companyName>Acme Capital Management</companyName> 
    <logoImg>1110</logoImg> 
    <pageHeader></pageHeader> 
</Home>' 


DECLARE @Result AS varchar(50) 

SET @result = @companyxml.value('(/Home/companyName/text())[1]','varchar(50)') 

SELECT @result