December 29, 2014 at 1:36 pm
Hello - I have a table with an XML datatype column in a SQL 2008 R2 database that I would like to retrieve the node value from. I have searched on this and should be able to simple get this value (without the need for a CROSS APPLY technique since there is only one element value in the node) by the following method:
SELECT [id],
[databasename],
[schemaname],
[objectname],
[objecttype],
[indexname],
[indextype],
[statisticsname],
[partitionnumber],
[extendedinfo].value('(/ExtendedInfo/Fragmentation)[1]', 'VARCHAR(MAX)')
AS
Fragmentation,
[command] = CASE
WHEN command LIKE '%REORGANIZE%' THEN 'REORGANIZE'
WHEN command LIKE '%REBUILD%' THEN 'REBUILD'
END,
[commandtype],
[starttime],
[endtime],
[errornumber],
[errormessage]
FROM [_DBAMain].[dbo].[commandlog]
Column 10 above is the 'extendedinfo' column (AS Fragmentation in result set), and is the XML datatype column. The values stored in the XML look like the following:
<ExtendedInfo>
<PageCount>156403</PageCount>
<Fragmentation>73.3387</Fragmentation>
</ExtendedInfo>
I simply want to grab the Fragmentation value from the XML for my result set, but I keep getting NULL values for every row (see attached).
Any insight would be greatly appreciated.
December 30, 2014 at 3:23 am
Hi. At face value, the xpath is correct for the xml example that you have posted so at this point I'd go and do some sanity checks on the base table/schema etc.
What happens if you just select the xml column as per the below, are you getting xml back in column 2 but not column 3?
SELECT [id],
[extendedinfo],
[extendedinfo].value('(/ExtendedInfo/Fragmentation)[1]', 'VARCHAR(MAX)'),
FROM [_DBAMain].[dbo].[commandlog]
Is the column schema bound? posting the schema definition may help.
Normally when i get this type of issue either I have the xpath wrong (doesn't look that here) or there are namespaces involved.
December 30, 2014 at 11:25 am
You were correct on doing a sanity check...The query works fine in my PROD environment, but was not working over in my QA environment. Odd thing is I literally just created this table with the DDL from PROD, but I think something got fouled up with the data copy over to QA. It works fine now since I recreated the table and reloaded the data.
However - I now have a new problem...I need to convert that column to a DECIMAL(2,1) with ROUND precision. I am getting close using CAST twice, but I'm getting errors with the alias naming. It looks like this right now:
SELECT [id],
[databasename],
[schemaname],
[objectname],
[objecttype],
[indexname],
[indextype],
[statisticsname],
[partitionnumber],
CAST(CAST([extendedinfo].value('(/ExtendedInfo/Fragmentation)[1]', 'VARCHAR(MAX)') + '%' AS 'extendedinfo' AS DECIMAL(2,1))),
[command] =
CASE
WHEN command LIKE '%REORGANIZE%' THEN 'REORGANIZE'
WHEN command LIKE '%REBUILD%' THEN 'REBUILD'
END,
[commandtype],
[starttime],
[endtime],
[errornumber],
[errormessage]
FROM [_DBAMain].[dbo].[commandlog]
The error states Incorrect syntax near "extendedinfo". Expecting DOUBLE, ID, NATIONAL, or QUOTED_ID.
I have tried to close the 'extendedinfo' column alias with double quotes, parens, brackets, but thus far - no luck in clearing this error. Perhaps I am going about this the wrong way? I am just trying to convert it to DECIMAL right now, and will address the ROUNDing there after. I have also tried using CONVERT but have had even less luck there. If I need to take this over to another forum specific to T-SQL, I will do that as well (just let me know).
December 30, 2014 at 3:35 pm
Hi, glad you got to the bottom of it!.
I think I understand what you are trying to do i.e. show the fragmentation as a percentage i.e. 85.5%? If so then this should work as the column definition for extendedInfo:
SELECT CONVERT(VARCHAR(10), [extendedinfo].value('(/ExtendedInfo/Fragmentation)[1]', 'DECIMAL(4,1)') )+'%' AS 'extendedinfo'
FROM [_DBAMain].[dbo].[commandlog]
December 30, 2014 at 3:56 pm
That is exactly what I was looking to do, and thanks to you - I am now doing exactly that!
Thank you again Arthur, and Happy New Year to you!
😀
December 31, 2014 at 4:41 am
No problem, thanks for the feedback.. Happy new year to you too.
January 23, 2015 at 2:44 pm
Just a quick performance note; when returning a text value from XML, you should specify the text() node for better performance.
For example, this:
[extendedinfo].value('(/ExtendedInfo/Fragmentation)[1]', 'DECIMAL(4,1)') )+'%' AS 'extendedinfo'
should be this:
[extendedinfo].value('(/ExtendedInfo/Fragmentation/text())[1]', 'DECIMAL(4,1)') )+'%' AS 'extendedinfo'
If you compare the two, you will see that the query that specifies the text node in your XPath expression produces a better query plan. 😉
-- Itzik Ben-Gan 2001
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply