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

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开发者_如何学Go

Thanks in advance.

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)