March 14, 2018 at 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 followingSELECT 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
March 14, 2018 at 12:22 pm
Douglas Osborne-229812 - Wednesday, March 14, 2018 10:21 AMSQL 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
March 14, 2018 at 1:00 pm
Douglas Osborne-229812 - Wednesday, March 14, 2018 10:21 AMSQL 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.
-- Itzik Ben-Gan 2001
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy