从Sql Server中的XML中将所有子节点作为列返回

Question

下面是从XML字符串中选择节点的步骤，第一部分只是向您展示我要从其中选择什么。

问题是，我想对各种不同的XML列这样做，我不想为我选择的每个列指定节点名称，是否有一种方法可以自动地将所有节点选择为列，甚至可以使用计数来选择游标？

DECLARE @MyXML XML
SET @MyXML = (SELECT 
            CAST (
        '<AllowAdd>N</AllowAdd>
        <Allowed>NUMSEG</Allowed>
        <AllSegmentsEqualValue>N</AllSegmentsEqualValue>
        <ClusterLevelSA>Y</ClusterLevelSA>
        <ClusterLevelPremium>Y</ClusterLevelPremium>
        <AllowAssignedAndInTrust>N</AllowAssignedAndInTrust>
        <MinSegments>1</MinSegments>
        <MaxSegments>100</MaxSegments>
        <DefaultSegments>10</DefaultSegments>
        <RoundPremiumsTo>2</RoundPremiumsTo>
        <TaxDeferredAllowance>0.05</TaxDeferredAllowance>
        <HigherTaxValueBands>HTVB</HigherTaxValueBands>
        <NumberYearsCalculationType>NONFIN</NumberYearsCalculationType>
        <OnShore>POLICY</OnShore>
        <OffShore>NONFIN</OffShore>'as XML) as x)

        SELECT 
        Data.Col.value('(/AllowAdd)[1]','Varchar(10)') as [Allow Addition of]
        ,Data.Col.value('(/Allowed)[1]','Varchar(10)') as [Allowed]
        ,Data.Col.value('(/MinSegments)[1]','Int') as [Min Segments]
        ,Data.Col.value('(/MaxSegments)[1]','Int') as [Max Segments]
        ,Data.Col.value('(/DefaultSegments)[1]','Int') as [Default Segments]
        ,Data.Col.value('(/RoundPremiumsTo)[1]','Int') as [Round Premiums To]
        ,Data.Col.value('(/AllSegmentsEqualValue)[1]','Varchar(10)') as [All Segments Equal Value]
        --,Data.Col.value('(/TaxDeferredAllowance)[1]','Varchar(10)') as [Tax Deferred Allowance]
        ,Data.Col.value('(/HigherTaxValueBands)[1]','Varchar(10)') as [Higher Tax Value Bands]
        ,Data.Col.value('(/NumberYearsCalculationType)[1]','Varchar(10)') as [Number Years Calculation Type]
        ,Data.Col.value('(/OnShore)[1]','Varchar(10)') as [OnShore]
        ,Data.Col.value('(/OffShore)[1]','Varchar(10)') as [OffShore]
        FROM @MyXML.nodes('/OffShore') AS Data(Col)

Answer 1

我希望，这就是你在等待的:)

DECLARE @MyXML XML
SET @MyXML = (SELECT 
            CAST (
        '<AllowAdd>N</AllowAdd>
        <Allowed>NUMSEG</Allowed>
        <AllSegmentsEqualValue>N</AllSegmentsEqualValue>
        <ClusterLevelSA>Y</ClusterLevelSA>
        <ClusterLevelPremium>Y</ClusterLevelPremium>
        <AllowAssignedAndInTrust>N</AllowAssignedAndInTrust>
        <MinSegments>1</MinSegments>
        <MaxSegments>100</MaxSegments>
        <DefaultSegments>10</DefaultSegments>
        <RoundPremiumsTo>2</RoundPremiumsTo>
        <TaxDeferredAllowance>0.05</TaxDeferredAllowance>
        <HigherTaxValueBands>HTVB</HigherTaxValueBands>
        <NumberYearsCalculationType>NONFIN</NumberYearsCalculationType>
        <OnShore>POLICY</OnShore>
        <OffShore>NONFIN</OffShore>'as XML) as x)

DECLARE @Output nvarchar(max) = N''
DECLARE @PivotList nvarchar(max)


SELECT 
    @PivotList = COALESCE(@PivotList + ', ', N'') + N'[' + XC.value('local-name(.)', 'varchar(100)') + N']'
FROM 
    @MyXML.nodes('/*') AS XT(XC)

SET @Output = N'SELECT 
    '+@PivotList+N'
FROM
(
    SELECT 
       ColName = XC.value(''local-name(.)'', ''nvarchar(100)''),
       ColValue = ISNULL(NULLIF(CONVERT(nvarchar(max),XC.query(''./*'')),''''),XC.value(''.'',''nvarchar(max)''))
    FROM 
       @MyXML.nodes(''/*'') AS XT(XC)
) AS s
PIVOT
(
    MAX(ColValue)
    FOR ColName IN ('+@PivotList+N')
) AS t;'

EXEC sp_executesql @Output, N'@MyXml xml', @MyXML = @MyXML;

Answer 2

给定输入XML，您可以尝试使用以下方法：

SELECT 
    ColName = XC.value('local-name(.)', 'varchar(100)'),
    ColValue = xc.value('(.)[1]', 'varchar(100)')
FROM 
    @MyXML.nodes('/*') AS XT(XC)

这将输出根下的每个XML元素--其名称和值--作为一个列表：

​

​

当然，因为这是一种非常通用的方法，所以您不能真正为第二个xc.value()中的每个列定义正确的数据类型--基本上可以将所有内容作为字符串。

Answer 3

使用游标构建SELECT语句，使用sp_executesql执行它！

    DECLARE @MyXML XML
    DECLARE @FieldName VARCHAR(MAX)
    DECLARE @SELECT_TEXT NVARCHAR(MAX)

    SET @MyXML = (SELECT 
                    CAST (
                '<AllowAdd>N</AllowAdd>
    <Allowed>NUMSEG</Allowed>
    <AllSegmentsEqualValue>N</AllSegmentsEqualValue>
    <ClusterLevelSA>Y</ClusterLevelSA>
    <ClusterLevelPremium>Y</ClusterLevelPremium>
    <AllowAssignedAndInTrust>N</AllowAssignedAndInTrust>
    <MinSegments>1</MinSegments>
    <MaxSegments>100</MaxSegments>
    <DefaultSegments>10</DefaultSegments>
    <RoundPremiumsTo>2</RoundPremiumsTo>
    <TaxDeferredAllowance>0.05</TaxDeferredAllowance>
    <HigherTaxValueBands>HTVB</HigherTaxValueBands>
    <NumberYearsCalculationType>NONFIN</NumberYearsCalculationType>
    <OnShore>POLICY</OnShore>
    <OffShore>NONFIN</OffShore>
    '
                    as XML) as x)


    SET @SELECT_TEXT = 'SELECT '

    DECLARE xml_cursor CURSOR
        FOR SELECT Data.Col.value( 'fn:local-name(.)', 'VARCHAR(MAX)' ) FROM @MyXML.nodes('/*') AS Data(Col)

    open xml_cursor
    While 1 = 1
    BEGIN
        fetch next from xml_cursor into @FieldName

        if @@fetch_status <> 0
        begin
                break
        end

        SET @SELECT_TEXT = @SELECT_TEXT + 'Data.Col.value(''(/' + @FieldName + ')[1]'',''Varchar(MAX)'') as [' + @FieldName + ']' + ', '

    END

    close xml_cursor 
    deallocate xml_cursor 

    SET @SELECT_TEXT = SUBSTRING( @SELECT_TEXT,0, LEN(@SELECT_TEXT) ) + 'FROM @MyXML.nodes(''/OffShore'') AS Data(Col)'

    EXECUTE sp_executesql @SELECT_TEXT, N'@MyXML XML', @MyXML = @MyXML

正如前面在另一个注释中提到的，它的缺点是没有列类型，但是它用于报表样式的查询，所以字符串是可以的。