Parsing xml in sql server

Question

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

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.

Kiran1016 · Accepted Answer · 2013-01-22 18:36:08Z

3

try this

 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

edited Jan 22, 2013 at 18:36

answered Jan 22, 2013 at 18:17

Kiran1016

9121 gold badge4 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

neil1967 Over a year ago

at the risk of being a noob, this sets @result to "<companyName>Acme Capital Management</companyName>" whereas I just need the company name "Acme Capital Management". What can I do to that select to give me the value without the tags?

Kiran1016 Over a year ago

Updated the query,check once

Collectives™ on Stack Overflow

Parsing xml in sql server

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related