Find the nth occurrence of a pattern in SQL Server

Question

I need to extract the value in 2nd occurrence of <spot> and <Points> tags. I tried different approaches with sub string and pattern index functions with out success.

<?xml version="1.0" encoding="utf-16"?>
<FSSTradeDetails xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <Markup>
        <Price>
            <PriceType>A</PriceType>
            <Wholesale>1.2772663500412138</Wholesale>
            <WholesaleRateComposition>
                <Rate>
                    <Terms>A</Terms>
                    <CurPair>GBP-USD</CurPair>
                    <Spot>1.61125</Spot>
                    <Points>157.95740000000168</Points>
                    <Forward>1.6270457400000002</Forward>
                </Rate>
                <Rate>
                    <Terms>A</Terms>
                    <CurPair>GBP-EUR</CurPair>
                    <Spot>1.2648663500412138</Spot>
                    <Points>124</Points>
                    <Forward>1.2772663500412138</Forward>
                </Rate>
                <Rate>
                    <Terms>A</Terms>
                    <CurPair>EUR-USD</CurPair>
                    <Spot>1.27385</Spot>
                    <Points>0</Points>
                    <Forward>1.27385</Forward>
                </Rate>
                <RateStates>
                    <ContractCounter>DEf</ContractCounter>
                    <ContractVia>Dff</ContractVia>
                    <CounterVia>DDf</CounterVia>
                </RateStates>
            </WholesaleRateComposition>
            <Markup>12</Markup>
            <Quote>1.27846635004121</Quote>
        </Price>
        <Profit>
            <Amount>1.53</Amount>
            <SpotRate>1.27385</SpotRate>
            <Terms>A</Terms>
            <NativeAmount>1.2000000000000455</NativeAmount>
            <NativeCurrency>EUR</NativeCurrency>
        </Profit>
    </Markup>
    <WholesaleRiskTransfer>
        <Transfer>No</Transfer>
    </WholesaleRiskTransfer>
</FSSTradeDetails>

This is the query I've tried:

SELECT 
   convert(DECIMAL(38, 8), 
           substring(DETAILS, CASE 
                                 WHEN PATINDEX('%<Spot>%', DETAILS) + 6 = 6
                                 THEN 0
                                 ELSE PATINDEX('%<Spot>%', DETAILS) + 6
                               END, 
   (PATINDEX('%</Spot>%', DETAILS) - (CASE 
                        WHEN PATINDEX('%<Spot>%', DETAILS) + 6 = 6
                            THEN 0
                        ELSE PATINDEX('%<Spot>%', DETAILS) + 6
                        END
                    )
                ))) AS [Spot Rate]

Any help is appreciated.

Answer 1

I suggest you use XML methods for this task:

DECLARE @xml xml = N'<FSSTradeDetails xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Markup><Price><PriceType>A</PriceType><Wholesale>1.2772663500412138</Wholesale><WholesaleRateComposition><Rate><Terms>A</Terms>
<CurPair>GBP-USD</CurPair><Spot>1.61125</Spot><Points>157.95740000000168</Points><Forward>1.6270457400000002</Forward></Rate><Rate><Terms>A</Terms>
<CurPair>GBP-EUR</CurPair><Spot>1.2648663500412138</Spot><Points>124</Points><Forward>1.2772663500412138</Forward></Rate><Rate><Terms>A</Terms>
<CurPair>EUR-USD</CurPair><Spot>1.27385</Spot><Points>0</Points><Forward>1.27385</Forward></Rate><RateStates><ContractCounter>DEf</ContractCounter>
<ContractVia>Dff</ContractVia><CounterVia>DDf</CounterVia></RateStates></WholesaleRateComposition><Markup>12</Markup><Quote>1.27846635004121</Quote>
</Price><Profit><Amount>1.53</Amount><SpotRate>1.27385</SpotRate><Terms>A</Terms><NativeAmount>1.2000000000000455</NativeAmount>
<NativeCurrency>EUR</NativeCurrency></Profit></Markup><WholesaleRiskTransfer><Transfer>No</Transfer></WholesaleRiskTransfer></FSSTradeDetails>'

SELECT
      WholesaleRateComposition.value('Rate[2]/Spot[1]', 'decimal(38, 20)') AS SecondSpot
    , WholesaleRateComposition.value('Rate[2]/Points[1]', 'decimal(38, 20)') AS SecondPoints
FROM @xml.nodes('/FSSTradeDetails/Markup/Price/WholesaleRateComposition') AS Price(WholesaleRateComposition);

To get the nth occurrence, you'll need to use dynamic SQL since the ordinals must be static:

DECLARE @Occurance nvarchar(5) = '3';
DECLARE @Sql nvarchar(MAX) = 
'SELECT
      WholesaleRateComposition.value(''Rate[' + @Occurance + ']/Spot[1]'', ''decimal(38, 20)'') AS SecondSpot
    , WholesaleRateComposition.value(''Rate[' + @Occurance + ']/Points[1]'', ''decimal(38, 20)'') AS SecondPoints
FROM @xml.nodes(''/FSSTradeDetails/Markup/Price/WholesaleRateComposition'') AS Price(WholesaleRateComposition);'

EXEC sp_executesql @Sql, N'@xml xml', @xml = @xml;