Issue retrieving XML value in result set...

  • SQL_ME_RICH

    SSChampion

    Points: 11200

    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.

  • Arthur Olcot

    SSCertifiable

    Points: 6008

    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.

  • SQL_ME_RICH

    SSChampion

    Points: 11200

    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).

  • Arthur Olcot

    SSCertifiable

    Points: 6008

    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]

  • SQL_ME_RICH

    SSChampion

    Points: 11200

    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!

    😀

  • Arthur Olcot

    SSCertifiable

    Points: 6008

    No problem, thanks for the feedback.. Happy new year to you too.

  • Alan Burstein

    SSC Guru

    Points: 61079

    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. 😉

    "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

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply