Help with XQuery modify()

• G_Dubyah

  Mr or Mrs. 500

  Points: 584

  More actions

  November 11, 2011 at 5:28 am

  #250544

  Hi Guys

  Can someone tell me why this does not work:

  DECLARE @XmlData xml

  DECLARE @NewValue varchar(50)

  SET @XmlData =

  ' <Header xmlns="http://www.namespace.co.uk/1.0">

  <Type code="Customer" />

  <Summary>Joe Bloggs</Summary>

  <ProcessVersion>11</ProcessVersion>

  <CreatedDate>2011-09-14</CreatedDate>

  <LastUpdatedDate>2011-10-06T13:14:00</LastUpdatedDate>

  <CurrentStatus code="Complete">Review Completed</CurrentStatus>

  </Header>

  '

  SET @NewValue = 'XXXXXX';

  SET @XmlData.modify('replace value of (/Header/Summary/text())[1] with sql:variable("@NewValue")') ;

  SELECT @XmlData

  And this does? (The header with namespaces is included which makes it not work)

  DECLARE @XmlData xml

  DECLARE @NewValue varchar(50)

  SET @XmlData =

  ' <Type code="Customer" />

  <Summary>Joe Bloggs</Summary>

  <ProcessVersion>11</ProcessVersion>

  <CreatedDate>2011-09-14</CreatedDate>

  <LastUpdatedDate>2011-10-06T13:14:00</LastUpdatedDate>

  <CurrentStatus code="Complete">Review Completed</CurrentStatus>

  '

  SET @NewValue = 'XXXXXX';

  SET @XmlData.modify('replace value of (/Summary/text())[1] with sql:variable("@NewValue")') ;

  SELECT @XmlData

  results

  <Type code="Customer" />

  <Summary>XXXXXX</Summary>

  <ProcessVersion>11</ProcessVersion>

  <CreatedDate>2011-09-14</CreatedDate>

  <LastUpdatedDate>2011-10-06T13:14:00</LastUpdatedDate>

  <CurrentStatus code="Complete">Review Completed</CurrentStatus>

• seth delconte

  SSCertifiable

  Points: 6388

  More actions

  November 11, 2011 at 9:42 am

  #1406419

  It's because of the xml namespace declaration. Try using WITH XMLNAMESPACE to locate the node:

  declare @XmlData xml

  declare @xml table (xmldocument xml)

  declare @NewValue varchar(50)

  set @NewValue = 'XXXXXX';

  insert @xml select ' <Header xmlns="http://www.namespace.co.uk/1.0">

  <Type code="Customer" />

  <Summary>Joe Bloggs</Summary>

  <ProcessVersion>11</ProcessVersion>

  <CreatedDate>2011-09-14</CreatedDate>

  <LastUpdatedDate>2011-10-06T13:14:00</LastUpdatedDate>

  <CurrentStatus code="Complete">Review Completed</CurrentStatus>

  </Header>'

  ;with XMLNAMESPACES (default 'http://www.namespace.co.uk/1.0' )

  update @xml

  set Xmldocument.modify('replace value of (/Header/Summary/text())[1] with sql:variable("@NewValue")')

  set @XmlData = (select * from @xml)

  select @XmlData

  go

  I also moved the XML string into an XML table during the manipulation.

  _________________________________
  seth delconte
  http://sqlkeys.com