Returning all child nodes as columns from XML in Sql Server

Question

I am doing the following to select nodes from an XML string, the first part is just to show you what I'm selecting from.

The issue is I want to do this for various different XML columns and I'd like to not have to specify the node name for each column in my select, is there a way to select all nodes as columns automatically or even a cursor using count?

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

I hope, this is what you are waiting for :)

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;