SQL Server 从不带标记名称的 XML 列中提取数据

您可以使用类似以下内容的内容：

SELECT
    N.C.value('let $i := . return count(//xml_line[. << $i]) + 1', 'int') as LineNumber,
    Item.Node.value('local-name(.)', 'varchar(max)') name,
    Item.Node.value('.', 'varchar(max)') value
FROM 
    table1
CROSS APPLY 
    data_xml.nodes('//xml_line') N(C)
CROSS APPLY 
    N.C.nodes('*') Item(Node)

要获取：

请参阅此 db<>fiddle。

但是，若要水平分布列，需要在查询不同的元素名称后生成动态 SQL。

附录：这是一个更新的 db<>fiddle，它还显示了一个动态 SQL 示例。

上面将所有值映射为 。如果您有数据，则可以进行适当的更改。如果需要将特定列映射到特定类型，则需要显式定义和填充名称到类型的映射表，并将其合并到动态 SQL 逻辑中。如果您希望结果列按特定顺序排列，则可能需要这样做。VARCHAR(MAX)NVARCHAR

附录 2：此更新的 db<>fiddle 现在包括列类型和排序逻辑。

--------------------------------------------------
-- Extract column names
--------------------------------------------------

DECLARE @Names TABLE (name VARCHAR(100))

INSERT @Names   
SELECT DISTINCT Item.Node.value('local-name(.)', 'varchar(max)')
FROM table1
CROSS APPLY data_xml.nodes('//xml_line/*') Item(Node)

--SELECT * FROM @Names

--------------------------------------------------
-- Define column-to-type mapping
--------------------------------------------------

DECLARE @ColumnTypeMap TABLE ( ColumnName SYSNAME, ColumnType SYSNAME, ColumnOrder INT)
INSERT @ColumnTypeMap
VALUES
    ('col1', 'int', 1),
    ('col2', 'varchar(10)', 2)

DECLARE @ColumnTypeDefault SYSNAME = 'varchar(max)'

--------------------------------------------------
-- Define SQL Templates
--------------------------------------------------

DECLARE @SelectItemTemplate VARCHAR(MAX) =
'    , N.C.value(<colpath>, <coltype>) <colname>
'

DECLARE @SqlTemplate VARCHAR(MAX) =
'SELECT
    N.C.value(''let $i := . return count(//xml_line[. << $i]) + 1'', ''int'') as LineNumber
<SelectItems>
FROM 
    table1
CROSS APPLY 
    data_xml.nodes(''//xml_line'') N(C)
'

--------------------------------------------------
-- Expand SQL templates into SQL
--------------------------------------------------

DECLARE @SelectItems VARCHAR(MAX) = (
    SELECT STRING_AGG(SI.SelectItem, '')
              WITHIN GROUP(ORDER BY ISNULL(T.ColumnOrder, 999), N.Name)
    FROM @Names N
    LEFT JOIN @ColumnTypeMap T ON T.ColumnName = N.name
    CROSS APPLY (
        SELECT SelectItem = REPLACE(REPLACE(REPLACE(
            @SelectItemTemplate
            , '<colpath>', QUOTENAME(N.name + '[1]', ''''))
            , '<colname>', QUOTENAME(N.name))
            , '<coltype>', QUOTENAME(ISNULL(T.ColumnType, @ColumnTypeDefault), ''''))
    ) SI(SelectItem)
)

DECLARE @Sql VARCHAR(MAX) = REPLACE(@SqlTemplate, '<SelectItems>', @SelectItems)

--------------------------------------------------
-- Execute
--------------------------------------------------

SELECT DynamicSql = @Sql

EXEC (@Sql)

结果（带有一些附加数据）：