trying to update an xml value!!

• TroyG

  SSCrazy

  Points: 2633

  More actions

  June 6, 2007 at 7:01 pm

  #176981

  Hi,

  I have an xml column which is holding the following type/structure of value:

  <

  LfxIVR xmlns="http://www.myurl.com/Schema.xsd">

  <

  LfxHeader>

  <

  LfxDocDesc>LFXIVR</LfxDocDesc>

  <

  LfxQueDest />

  <

  LfxSource />

  <

  LfxTranTime>2007-06-07T12:05:24.447+12:00</LfxTranTime>

  <

  LFXDocOrigin>TDS</LFXDocOrigin>

  </

  LfxHeader>

  <

  LfxData>

  <

  Event>abc</Event>

  <

  ConsignmentNo>29495</ConsignmentNo>

  <

  PickUpEvents />

  <

  DeliveryEvents>

  <

  UnloadPodSigned>TRUE</UnloadPodSigned>

  <

  DepartTime>2007-06-07T12:05:24.447+12:00</DepartTime>

  </

  DeliveryEvents>

  <

  Exception />

  <

  OtherEvent />

  </

  LfxData>

  </

  LfxIVR>

  When trying to update a specific node (or element or whatever it is called) within this column, I never actually get a match using the following type of where clause:

  select

  *

  from

  dbo.fms_lfxivr_log

  where

  fms_interface_xml.value('(/LfxIVR/LfxData/Event)[1]', 'varchar(max)') = 'abc'

  I presume my format of my where clause isn't right - but can't quite see what it is...can anyone assist with where I am going wrong?! Am thinking it must have something to do with the top level....

  Thanks in advance

  Troy

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  June 7, 2007 at 1:21 am

  #711330

  I think it has something to do with the schema. When I took the schema definition out of your XML, the select returned one row.

  I haven't had that much expereience with XML and virtually none with scehmas, but maybe that will point you in the right direction. I'll see if I can figure it ou.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• TroyG

  SSCrazy

  Points: 2633

  More actions

  June 7, 2007 at 2:27 pm

  #711528

  Thanks Gail for the reply. Yeah I had sort of figured that it was something to do with the schema definitiion, but if I remove it, the statement doesn't return anything (ie as if the node/element just doesn't exist). And well if I leave it in there, if returns an error relating to incorrect syntax

  Thanks again for your help - if you have any ideas please let me know - as I am at a bit of a loss.

  Thanks

  Troy

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  June 7, 2007 at 11:51 pm

  #711589

  When I tried it, with the schema I got no rows, without it your query returned one row.

  Is the xml column bound to a schema?

  -- Test code

  CREATE

  TABLE #xmlTest (

  TestCol XML

  )

  -- one row with the schema definition, one without

  INSERT INTO #xmlTest VALUES

  ('<LfxIVR xmlns="http://www.myurl.com/Schema.xsd"><LfxHeader>

  <LfxDocDesc>LFXIVR</LfxDocDesc>

  <LfxQueDest />

  <LfxSource />

  <LfxTranTime>2007-06-07T12:05:24.447+12:00</LfxTranTime>

  <LFXDocOrigin>TDS</LFXDocOrigin>

  </LfxHeader>

  <LfxData>

  <Event>abc</Event>

  <ConsignmentNo>29495</ConsignmentNo>

  <PickUpEvents />

  <DeliveryEvents>

  <UnloadPodSigned>TRUE</UnloadPodSigned>

  <DepartTime>2007-06-07T12:05:24.447+12:00</DepartTime>

  </DeliveryEvents>

  <Exception />

  <OtherEvent />

  </LfxData>

  </LfxIVR>')

  INSERT

  INTO #xmlTest VALUES

  ('<LfxIVR>

  <LfxHeader>

  <LfxDocDesc>LFXIVR</LfxDocDesc>

  <LfxQueDest />

  <LfxSource />

  <LfxTranTime>2007-06-07T12:05:24.447+12:00</LfxTranTime>

  <LFXDocOrigin>TDS</LFXDocOrigin>

  </LfxHeader>

  <LfxData>

  <Event>abc</Event>

  <ConsignmentNo>29495</ConsignmentNo>

  <PickUpEvents />

  <DeliveryEvents>

  <UnloadPodSigned>TRUE</UnloadPodSigned>

  <DepartTime>2007-06-07T12:05:24.447+12:00</DepartTime>

  </DeliveryEvents>

  <Exception />

  <OtherEvent />

  </LfxData>

  </LfxIVR>')

  select

  * from #xmlTest

  where TestCol.value('(/LfxIVR/LfxData/Event)[1]', 'varchar(max)') = 'abc'

  (1 row(s) affected)

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• TroyG

  SSCrazy

  Points: 2633

  More actions

  June 10, 2007 at 4:48 pm

  #711884

  Thanks again for the response - yes I get the same response as you when removing the namespace reference out of the xml.

  I cannot remove the reference out of the xml, as this is generated by and for a 3rd party application, so cannot effect how this is written etc.

  this is looking to be one of those 'too hard' basket things....

  Any ideas?

  Thanks

  Troy

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  June 12, 2007 at 12:01 am

  #712173

  I'll dig into schemas, if I get a chance. See if I can figure anything out. XML is not something I've worked much with though.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass