XML namespaces?

• Ray Metz

  SSC-Addicted

  Points: 438

  More actions

  November 2, 2005 at 1:13 pm

  #166613

  I'm trying to get data out on an Excel spreadsheet saved as XML.  After removing the namespaces from the XML, this first statement works great...

  declare

  @x xml

  set

  @x = '

  <?mso-application progid="Excel.Sheet"?>

  <Workbook>

  <Worksheet Name="Sheet1">

  </Worksheet>

  <Worksheet Name="Sheet2">

  </Worksheet>

  <Worksheet Name="Sheet3">

  </Worksheet>

  </Workbook>

  '

  SELECT

  c

  .value('@Name', 'varchar(100)') WorksheetName

  FROM

  @x.nodes('/Workbook/Worksheet') AS t(c)

   

  I get a list of worksheet names just fine.

  But when I leave the namespace stuff in the XML, it fails:

  set

  @x = '

  <?mso-application progid="Excel.Sheet"?>

  <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"

  xmlns:o="urn:schemas-microsoft-com:office:office"

  xmlns:x="urn:schemas-microsoft-com:office:excel"

  xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"

  xmlns:html="http://www.w3.org/TR/REC-html40">

  <Worksheet ss:Name="Sheet1">

  </Worksheet>

  <Worksheet ss:Name="Sheet2">

  </Worksheet>

  <Worksheet ss:Name="Sheet3">

  </Worksheet>

  </Workbook>

  '

  --

  SELECT

  c

  .value('@ss:Name', 'varchar(100)') WorksheetName

  FROM

  @x.nodes('/Workbook/Worksheet') AS t(c)

  This statement fails.

  I tried using the WITH NAMESPACES syntax, but I have no clue if that's the right thing to do or not.

  --WITH XMLNAMESPACES 'xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40"'

   

  Any help is appriciated.

  Ray Metz

• Ray Metz

  SSC-Addicted

  Points: 438

  More actions

  November 2, 2005 at 2:32 pm

  #601987

  Some Microsoft developers gave me three solutions...

   

  declare

  @x xml

  set

  @x = (select b.BulkColumn from openrowset (BULK 'c:\Book1.xml',SINGLE_CLOB) b)

  SELECT

  c

  .value('declare namespace ss="urn:schemas-microsoft-com:office:spreadsheet"; @ss:Name', 'varchar(100)') WorksheetName

  FROM

  @x.nodes('declare default element namespace "urn:schemas-microsoft-com:office:spreadsheet";/Workbook/Worksheet') AS t(c)

  ;

  WITH

  XMLNAMESPACES(

  DEFAULT 'urn:schemas-microsoft-com:office:spreadsheet',

  'urn:schemas-microsoft-com:office:office' AS o,

  'urn:schemas-microsoft-com:office:excel' AS x,

  'urn:schemas-microsoft-com:office:spreadsheet' AS ss,

  'http://www.w3.org/TR/REC-html40' AS html)

  SELECT

  c

  .value('@ss:Name', 'varchar(100)') WorksheetName

  FROM

  @x.nodes('/Workbook/Worksheet') AS t(c)

  ;

  WITH

  XMLNAMESPACES(

  DEFAULT 'urn:schemas-microsoft-com:office:spreadsheet',

  'urn:schemas-microsoft-com:office:spreadsheet' AS ss)

  SELECT

  c

  .value('@ss:Name', 'varchar(100)') WorksheetName

  FROM

  @x.nodes('/Workbook/Worksheet') AS t(c)