在select语句中解析存储在表记录中的xml - SQL Server

Question

我在尝试解析存储在表记录中的 xml 时遇到了问题 xml 结构如下：

<?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>

xml 以 varchar 形式存储在表记录中。

我需要获取临时表，其中包含 xml 结构中不同的 WidgetId 集。

更新：

我确实编写了以下批处理语句来检索 WidgetConfiguration xml 字符串上的集合，因此我能够检索 WidgetId 集，但我遇到了插入语句的问题：

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'在“cast”结果上调用“nodes”时，最后一个插入语句行出现语法错误

提前致谢。

Answer 1

尝试一下 - 这会起作用：

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)

所以基本上：

• 将 XML 存储为数据类型 XML - 或者如果不能，则必须将 VARCHAR 列转换为XML 进行处理

• 抓取// 节点列表作为“伪表”

• 提取该伪表的每个成员中的 元素作为 INT

更新： 好的，要从表中执行此操作，请使用以下命令：

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)