XML Question

• Ruprecht

  SSC Eights!

  Points: 968

  More actions

  May 24, 2012 at 2:38 pm

  #274439

  Hello all,

  I'm having some trouble selecting out some values from an XML column. I believe my issues are caused in

  part by the namespace being incorporated into each tag, but everything I try either results in a syntax error or a blank string.

  Here is the T-SQL I'm trying to use and a sample of the XML is attached.

  SELECT

  xml_column.query('data(/VehicleValue_Struc/VehicleYear)') AS vehicle_year

  FROM

  vehicles

  WHERE

  id = 10

  Thanks!

• seth delconte

  SSCertifiable

  Points: 6388

  More actions

  May 24, 2012 at 3:10 pm

  #1492831

  ;WITH xmlnamespaces (N'http://webservice.nada.com/' as ns)

  SELECT

  xml_column.value(N'(//VehicleValue_Struc/ns:VehicleYear)[1]',N'nvarchar(100)') AS vehicle_year

  FROM

  vehicles

  WHERE

  id = 10

  OR:

  ;WITH xmlnamespaces (N'http://webservice.nada.com/' as ns)

  SELECT

  xml_column.query(N'data(//VehicleValue_Struc/ns:VehicleYear)[1]') AS vehicle_year

  FROM

  vehicles

  WHERE

  id = 10

  _________________________________
  seth delconte
  http://sqlkeys.com

• Ruprecht

  SSC Eights!

  Points: 968

  More actions

  May 24, 2012 at 3:22 pm

  #1492837

  You, sir, are my hero. So you need to declare xmlnamespaces whenever the namespace is included in the XML tag?

  Thanks again!

• seth delconte

  SSCertifiable

  Points: 6388

  More actions

  May 24, 2012 at 3:27 pm

  #1492841

  Yes. You can also do it inline like this:

  SELECT

  xml_column.query('declare namespace ns="http://webservice.nada.com/"; data(//VehicleValue_Struc/ns:VehicleYear)[1]') AS vehicle_year

  FROM

  vehicles

  WHERE

  id = 10

  And you are welcome

  _________________________________
  seth delconte
  http://sqlkeys.com