How to name the output column from an XML query

• SQLOzzie

  Default port

  Points: 1446

  More actions

  March 14, 2018 at 10:21 am

  #333937

  SQL XML Gods,

  Everyone - how do I execute the following query and set the name of the output column?

  If I run the following
  SELECT DeviceModelID, DeviceModelName, DimensionID, DisplayName, CreatedBy, DateCreated, ModifiedBy, DateModified, IsDeleted, ModalityTypeID
INTO #tempHTML3
FROM dbo.DeviceModel

  SELECT
(
  SELECT *
  FROM #tempHTML3
  FOR XML PATH('tr'),TYPE
).query('for $tr in /tr
   return <tr>
    {
     for $td in $tr/*
     return <td>{$td/text()}</td>
    }
      </tr>')
FOR XML PATH('table')
  The column name of the resulting output is a system generated name like 'XML_F52E2B61-18A1-11d1-B105-00805' - I'd like it to be 'Data.'
  Should be easy
  SQLOzzie

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  March 14, 2018 at 12:22 pm

  #1983264

  Douglas Osborne-229812 - Wednesday, March 14, 2018 10:21 AM

  SQL XML Gods,

  Everyone - how do I execute the following query and set the name of the output column?

  If I run the following
  SELECT DeviceModelID, DeviceModelName, DimensionID, DisplayName, CreatedBy, DateCreated, ModifiedBy, DateModified, IsDeleted, ModalityTypeID
INTO #tempHTML3
FROM dbo.DeviceModel

  SELECT
(
  SELECT *
  FROM #tempHTML3
  FOR XML PATH('tr'),TYPE
).query('for $tr in /tr
   return <tr>
    {
     for $td in $tr/*
     return <td>{$td/text()}</td>
    }
      </tr>')
FOR XML PATH('table')
  The column name of the resulting output is a system generated name like 'XML_F52E2B61-18A1-11d1-B105-00805' - I'd like it to be 'Data.'
  Should be easy ☺
  SQLOzzie

  There should be a better way but one ugly option would be to wrap it all in another select to add the alias:
  SELECT
(
SELECT
(
SELECT *
FROM #tempHTML3
FOR XML PATH('tr'),TYPE
).query('for $tr in /tr
 return <tr>
  {
  for $td in $tr/*
  return <td>{$td/text()}</td>
  }
  </tr>')
FOR XML PATH('table'), TYPE
) as [Data]

  Sue

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  March 14, 2018 at 1:00 pm

  #1983276

  Douglas Osborne-229812 - Wednesday, March 14, 2018 10:21 AM

  SQL XML Gods,

  Everyone - how do I execute the following query and set the name of the output column?

  If I run the following
  SELECT DeviceModelID, DeviceModelName, DimensionID, DisplayName, CreatedBy, DateCreated, ModifiedBy, DateModified, IsDeleted, ModalityTypeID
INTO #tempHTML3
FROM dbo.DeviceModel

  SELECT
(
  SELECT *
  FROM #tempHTML3
  FOR XML PATH('tr'),TYPE
).query('for $tr in /tr
   return <tr>
    {
     for $td in $tr/*
     return <td>{$td/text()}</td>
    }
      </tr>')
FOR XML PATH('table')
  The column name of the resulting output is a system generated name like 'XML_F52E2B61-18A1-11d1-B105-00805' - I'd like it to be 'Data.'
  Should be easy ☺
  SQLOzzie

  Sue's solution is good. That said, can you post a copy of one or more of the XML records that you are working with? I think this should be a way to do this using the query method without a flowr (for let  order-by return) statement.

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001