query a column of xml files

• ertao0215

  SSC Enthusiast

  Points: 109

  More actions

  April 30, 2015 at 5:02 am

  #317191

  Hi,

  this problem perplex me for a long time, can you help me?

  I want to query a column of xml files in a table,

  use mysql1

  declare @bp xml

  select @bp=xml

  ;WITH XMLNAMESPACES('http://schemas.openehr.org/v1' as bp,'http://www.w3.org/2001/XMLSchema-instance' as xsi,'OBSERVATION' as type)

  select * from (

  select

  m.c.value('(./bp:data/bp:items[1]/bp:value[1]/bp:magnitude)[1]','int') as systolisch

  from

  BloodpressureMitSchema cross apply

  @bp.nodes('/bp:content/bp:data/bp:events') as m(c))m

  but with this "cross apply" I can only query all the values in one xml and repeat them. Is there something wrong at "declear"

  thanks all

• Eirikur Eiriksson

  SSC Guru

  Points: 182948

  More actions

  May 1, 2015 at 2:07 am

  #1794910

  Quick suggestion

  😎

  use mysql1

  ;WITH XMLNAMESPACES('http://schemas.openehr.org/v1' as bp,'http://www.w3.org/2001/XMLSchema-instance' as xsi,'OBSERVATION' as type)

  select * from (

  select

  m.c.value('(/bp:magnitude/text())','int') as systolisch

  from

  [schema_name].[table_name] [alias]

  cross apply [alias].[xml column name].nodes('/bp:content/bp:data/bp:events/bp:data/bp:items/bp:value') as m(c))m;

• ertao0215

  SSC Enthusiast

  Points: 109

  More actions

  May 1, 2015 at 6:07 am

  #1794945

  It works. Thanks alot 🙂

  I thought to much