在sql server中解析xml

2021-10-02 00:00:00 xml xml-parsing sql-server

我有一个包含 xml 的 ntext 类型列的表.我已尝试应用许多示例，说明如何从特定节点的 xml 中提取公司名称的值，但仍然出现语法错误.下面是我所做的，除了用我的 select 语句替换了实际的 xml 输出

I have a table with an ntext type column that holds xml. I have tried to apply many examples of how to pull the value for the company's name from the xml for a particular node, but continue to get a syntax error. Below is what I've done, except substituted my select statement for the actual xml output 

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

出于某种原因，select c.value 语句有一个我无法弄清楚的语法问题.在 SSMS 中悬停时，它说找不到列c"或用户定义的函数或聚合c.value"，或者名称不明确.'

For some reason, the select c.value statement has a syntax problem that I can't figure out. On hover in SSMS, it says 'cannot find either column "c" or the user-defined function or aggregate "c.value", or the name is ambiguous.'

对语法的任何帮助将不胜感激.

Any help on the syntax would be greatly appreciated.

推荐答案

试试这个

 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

相关文章