Fetch XML data stored in database table column dynamically

Please post a concise and complete example. Include table DDL and sample data INSERT statements.

Cause the solution depends on your actual structure. Otherwise take a look at the nodes() method.

E.g.

DECLARE @Sample TABLE
    (
      ID INT IDENTITY ,
      Data XML
    );

INSERT  INTO @Sample
        ( Data )
VALUES  ( '<RiskEndorsement><ExistingExposureSplitLimitsChange><NewRiskLimitSeq>536504</NewRiskLimitSeq><EffDate>11/1/2011</EffDate></ExistingExposureSplitLimitsChange></RiskEndorsement> ' ),
        ( '<RiskEndorsement><MandatoryStateRateSplit><StateCode>NY</StateCode><EffDate>4/1/2011</EffDate></MandatoryStateRateSplit></RiskEndorsement> ' ),
        ( '<RiskEndorsement><NonAnniversaryExModSplit><RiskBureauSeq>197608</RiskBureauSeq><RiskIDStatusSeq>1616</RiskIDStatusSeq><RiskIDNbr>0389463</RiskIDNbr><ExModStatusSeq>1607</ExModStatusSeq><ExModFactor>0.890</ExModFactor><SplitDate>3/11/2012</SplitDate></NonAnniversaryExModSplit></RiskEndorsement> ' ),
        ( '<RiskEndorsement><NonAnniversaryExModSplit><RiskBureauSeq>197613</RiskBureauSeq><RiskIDStatusSeq>1616</RiskIDStatusSeq><RiskIDNbr>0389463</RiskIDNbr><ExModStatusSeq>1607</ExModStatusSeq><ExModFactor>0.970</ExModFactor><SplitDate>1/13/2013</SplitDate></NonAnniversaryExModSplit></RiskEndorsement> ' ),
        ( '<RiskEndorsement><MandatoryStateRateSplit><StateCode>AL</StateCode><EffDate>7/1/2011</EffDate></MandatoryStateRateSplit></RiskEndorsement> ' ),
        ( '<RiskEndorsement><AnniversaryRatingDateSplit><RiskBureauSeq>208975</RiskBureauSeq><RiskIDStatusSeq>1616</RiskIDStatusSeq><RiskIDNbr>230094357</RiskIDNbr><ExModStatusSeq>1607</ExModStatusSeq><ExModFactor>0.8700</ExModFactor><SplitDate>1/1/2012</SplitDate></AnniversaryRatingDateSplit></RiskEndorsement> ' ),
        ( '<RiskEndorsement><AnniversaryRatingDateSplit><RiskBureauSeq>213467</RiskBureauSeq><RiskIDStatusSeq>1616</RiskIDStatusSeq><RiskIDNbr>2431638</RiskIDNbr><ExModStatusSeq>1607</ExModStatusSeq><ExModFactor>0.6800</ExModFactor><SplitDate>10/1/2013</SplitDate></AnniversaryRatingDateSplit></RiskEndorsement> ' ),
        ( '<RiskEndorsement><NonAnniversaryExModSplit><RiskBureauSeq>213473</RiskBureauSeq><RiskIDStatusSeq>1615</RiskIDStatusSeq><RiskIDNbr>917661014</RiskIDNbr><ExModStatusSeq>1607</ExModStatusSeq><ExModFactor>0.860</ExModFactor><SplitDate>10/1/2013</SplitDate></NonAnniversaryExModSplit></RiskEndorsement> ' ),
        ( '<RiskEndorsement><AnniversaryRatingDateSplit><RiskBureauSeq>213497</RiskBureauSeq><RiskIDStatusSeq>1616</RiskIDStatusSeq><RiskIDNbr>1146456</RiskIDNbr><ExModStatusSeq>1607</ExModStatusSeq><ExModFactor>0.830</ExModFactor><SplitDate>10/1/2013</SplitDate></AnniversaryRatingDateSplit></RiskEndorsement> ' );

SELECT  ID ,
        ExistingExposureSplitLimitsChange.value('NewRiskLimitSeq[1]', 'INT') AS NewRiskLimitSeq ,
        ExistingExposureSplitLimitsChange.value('EffDate[1]', 'DATE') AS EffDate
FROM    @Sample S
        CROSS APPLY S.Data.nodes('/RiskEndorsement/ExistingExposureSplitLimitsChange') A ( ExistingExposureSplitLimitsChange );

SELECT  ID ,
        MandatoryStateRateSplit.value('StateCode[1]', 'NVARCHAR(255)') AS StateCode ,
        MandatoryStateRateSplit.value('EffDate[1]', 'DATE') AS EffDate
FROM    @Sample S
        CROSS APPLY S.Data.nodes('/RiskEndorsement/MandatoryStateRateSplit') A ( MandatoryStateRateSplit );

SELECT  ID ,
                NonAnniversaryExModSplit.value('RiskBureauSeq[1]', 'INT') AS RiskBureauSeq ,
                NonAnniversaryExModSplit.value('RiskIDStatusSeq[1]', 'INT') AS RiskIDStatusSeq ,
                NonAnniversaryExModSplit.value('RiskIDNbr[1]', 'INT') AS RiskIDNbr ,
                NonAnniversaryExModSplit.value('ExModStatusSeq[1]', 'INT') AS ExModStatusSeq ,
                NonAnniversaryExModSplit.value('ExModFactor[1]', 'FLOAT') AS ExModFactor ,
                NonAnniversaryExModSplit.value('SplitDate[1]', 'NVARCHAR(255)') AS SplitDateText 
FROM    @Sample S
        CROSS APPLY S.Data.nodes('/RiskEndorsement/NonAnniversaryExModSplit') A ( NonAnniversaryExModSplit );

SELECT  ID ,        
                AnniversaryRatingDateSplit.value('RiskBureauSeq[1]', 'INT') AS RiskBureauSeq ,
                AnniversaryRatingDateSplit.value('RiskIDStatusSeq[1]', 'INT') AS RiskIDStatusSeq ,
                AnniversaryRatingDateSplit.value('RiskIDNbr[1]', 'INT') AS RiskIDNbr ,
                AnniversaryRatingDateSplit.value('ExModStatusSeq[1]', 'INT') AS ExModStatusSeq ,
                AnniversaryRatingDateSplit.value('ExModFactor[1]', 'FLOAT') AS ExModFactor ,
                AnniversaryRatingDateSplit.value('SplitDate[1]', 'NVARCHAR(255)') AS SplitDateText 
FROM    @Sample S
        CROSS APPLY S.Data.nodes('/RiskEndorsement/AnniversaryRatingDateSplit') A ( AnniversaryRatingDateSplit );