text() for xml Data Type Methods

• gan-685682

  Ten Centuries

  Points: 1216

  More actions

  September 2, 2008 at 4:14 am

  #212045

  I have a t-sql:

  exec @ret = uspAAA '<?xml version="1.0"? > < SerialNumber><S Descr="SerialNumber">2283 </S> </SerialNumber>'

  inside uspAAA it was doing this for MSSQL 2000 :

  insert @SSNField (N, V_xml)

  select N, D from openxml(@iDoc, '/SerialNumber/S') with (N varchar(200) '@Descr', D varchar(800) 'text()')

  to get the "2283"

  But what if I want to use XML datatype,

  insert @SSNField (N, V_xml)

  --select N, D from openxml(@iDoc, '/SerialNumber/S') with (N varchar(200) '@Descr', D varchar(800) 'text()')

  select

  tab.col.value('@Descr', 'varchar(100)'),

  ??<--what?

  from

  @xml.nodes('/SerialNumber/S') tab(col)

• gan-685682

  Ten Centuries

  Points: 1216

  More actions

  September 2, 2008 at 4:21 am

  #865908

  the one I could find is:

  insert @SSNField (N, V_xml)

  --select N, D from openxml(@iDoc, '/SerialNumber/S') with (N varchar(200) '@Descr', D varchar(800) 'text()')

  select

  tab.col.value('@Descr', 'varchar(100)'),

  convert(varchar, @xml.query('/SerialNumber/S/text()'))

  from

  @xml.nodes('/SerialNumber/S') tab(col)

  but I would prefer to refer to relative path, instead of specifying absolute path twice.

• Mark Cowne

  One Orange Chip

  Points: 26979

  More actions

  September 2, 2008 at 4:53 am

  #865918

  select

  tab.col.value('@Descr', 'varchar(100)'),

  tab.col.value('.', 'varchar(100)')

  from @xml.nodes('/SerialNumber/S') tab(col)

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• gan-685682

  Ten Centuries

  Points: 1216

  More actions

  September 2, 2008 at 7:01 am

  #865956

  thanks.