Parsing xml stored in table records in the select statement - SQL Server

Question

I've run into a problem while trying to parse the xml which is stored in the table records The xml structure is following :

<?xml version="1.0" encoding="utf-16"?>  
<WidgetsLayout xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
        <WidgetsList>      
            <WidgetConfiguration>        
                <WidgetId>4</WidgetId>        
                <DockOrder>0</DockOrder>        
                <DockZoneId>0</DockZoneId>        
                <Width />        
                <Height />        
                <IsRemovable>true</IsRemovable>      
            </WidgetConfiguration>      
            <WidgetConfiguration>        
                <WidgetId>3</WidgetId>        
                <DockOrder>0</DockOrder>        
                <DockZoneId>1</DockZoneId>        
                <Width />        
                <Height />        
                <IsRemovable>true</IsRemovable>      
            </WidgetConfiguration>    
        </WidgetsList>  
    </WidgetsLayout>

And the xml is stored as varchar in the table records.

I need to get the temporary table which will contain distinct set of WidgetId from the xml structure.

UPDATED :

I did write the following batch statement to retrieve the set on WidgetConfiguration xml strings, so I would be able to retrieve WidgetId set, but I've run into a problem with the insert statement:

GO

    declare @dashboard_layout table (
        id int,
        config_xml xml
    )

    INSERT INTO @dashboard_layout(id)
        SELECT 
            widget_config.value('(WidgetId)[1]', 'int')
        FROM
            dbo.dashboard_configuration c
        CROSS APPLY 
            CAST(RIGHT(c.configuration_xml_string, LEN(c.configuration_xml_string) - 41), XML).nodes('/WidgetsLayout/WidgetsList/WidgetConfiguration') AS list(widget_config)

    select * from @dashboard_layout

I've got an syntax error in last insert statement line when calling 'nodes' on 'cast' result

Thanks in advance.

marc_s · Accepted Answer · 2011-07-26T11:09:08.203

Try this - this would work:

DECLARE @input XML = '<WidgetsLayout xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
        <WidgetsList>      
            <WidgetConfiguration>        
                <WidgetId>4</WidgetId>        
                <DockOrder>0</DockOrder>        
                <DockZoneId>0</DockZoneId>        
                <Width />        
                <Height />        
                <IsRemovable>true</IsRemovable>      
            </WidgetConfiguration>      
            <WidgetConfiguration>        
                <WidgetId>3</WidgetId>        
                <DockOrder>0</DockOrder>        
                <DockZoneId>1</DockZoneId>        
                <Width />        
                <Height />        
                <IsRemovable>true</IsRemovable>      
            </WidgetConfiguration>    
        </WidgetsList>  
    </WidgetsLayout>'

SELECT
    WList.value('(WidgetId)[1]', 'int')
FROM
    @input.nodes('/WidgetsLayout/WidgetsList/WidgetConfiguration') AS Widget(WList)

So basically:

store your XML as datatype XML - or if you can't, you will have to convert your VARCHAR column to XML do to the processing
grab the list of <WidgetsLayout>/<WidgetsList>/<WidgetConfiguration> nodes as a "pseudo-table"
extract the <WidgetId> element from each of the members of that pseudo table as an INT

Update: OK, to do this from a table, use this:

INSERT INTO @dashboard_layout(ID)
    SELECT 
        WList.value('(WidgetId)[1]', 'int')
    FROM
        dbo.dashboard_configuration c
    CROSS APPLY 
        CAST(c.YourColumn AS XML).nodes('/WidgetsLayout/WidgetsList/WidgetConfiguration') AS Widget(WList)

the problem is that i have a set of rows of varchar type containing this xml stucture in dashboard_configuration table. So i insert all the xml strings from dashboard_configuration into a @dashboard_layout table and then I have parse each one of them and retrieve all the Widget_ids found. — danyloid, Jul 26 '11 at 11:04
i've tried your sample, but i get the syntax error at second '.' in the last line — danyloid, Jul 26 '11 at 11:30
Works if calling 'nodes' on xml-typed data field. had to write inner select statement. Thanks — danyloid, Jul 26 '11 at 11:51

Parsing xml stored in table records in the select statement - SQL Server

1 Answers1