How to replace XML queries in SQL Server?

Question

I got a question about replacing XML columns, here is an small example about what I like to change.

<i><recipes n="0" />
<CGrecipes cg="4" r0="302053" r1="302084" r2="302049" r3="302068" />
<HArecipes ha="4" r0="302103" r1="302083" r2="302050" r3="302087" />
<KHrecipes kh="10" r0="302100" r1="302090" r2="302078" r3="302074" 
                   r4="302094" r5="302082" r6="302066" r7="302051" 
                   r8="302086" r9="302070" />
<KHNrecipes khn="10" r0="302102" r1="302089" r2="302056" r3="302077" 
                     r4="302052" r5="302069" r6="302081" r7="302073" 
                     r8="302093" r9="302085" />
<IMITARrecipes imitar="2" r0="302110" r1="302057" />
<MAUSERrecipes mauser="1" r0="302106" />
<SVDrecipes svd="1" r0="302059" />
<BLASERrecipes blaser="2" r0="302105" r1="302060" />
<SIGSAUERrecipes sigsauer="2" r0="302109" r1="302061" />
<HONEYBADGERrecipes honeybadger="1" r0="302062" />
<SMALLBACKPACKrecipes smallbackpack="1" r0="302095" />
<MEDIUMBACKPACKrecipes mediumbackpack="1" r0="302096" />
<MILITARYBACKPACKrecipes militarybackpack="1" r0="302097" />
<LARGEBACKPACKrecipes largebackpack="1" r0="302098" />
<TEDDYBACKPACKrecipes teddybackpack="1" r0="302099" />
<ALICEBACKPACKrecipes alicebackpack="1" r0="302112" />
<M82recipes m82="1" r0="302107" />
<AWMrecipes awm="1" r0="302108" />
<B93Rrecipes b93r="1" r0="302111" />
</i>

And I want to change it with a script to:

<i><recipes r="0" r0="302053" r1="302084" r2="302049" r3="302068" r4="302103" r5="302083" r6="302050" r7="302087" r8="302100" r9="302090" r10="302078" r11="302074" r12="302094" r13="302082" r14="302066" r15="302051" r16="302086" r17="302070" r18="302102" r19="302089" r20="302056" r21="302077" r22="302052" r23="302069" r24="302081" r25="302073" r26="302093" r27="302085" r28="302110" r29="302057" r30="302106" r31="302059" r32="302105" r33="302060" r34="302109" r35="302061" r36="302062" r37="302095" r38="302096" r39="302097" r40="302098" r41="302099" r42="302112" r43="302107" r44="302108" r45="302111" /></i>

I would like to get help and suggestions!

Answer 1

Well, it couldn't be simpler (literally, I think), thanks to T-SQL's very limited implementation of XQuery, and the general hate for dynamic XML of any kind. Let @xml contain your XML in a variable (if it's a column, add a FROM as required).

SELECT CONVERT(XML, REPLACE((
    SELECT @xml.value('/i[1]/recipes[1]/@n', 'int') AS [@r], '' AS [@marker]
    FOR XML PATH('recipes'), ROOT('i')
), 'marker=""', (
    SELECT ' ' + REPLACE(REPLACE('r#="$v"', 
        '#', ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1), 
        '$v', t.value('text()[1]', 'int')) 
    FROM (
        SELECT @xml.query('
            for $a in //*/@*[substring(local-name(),1,1)="r"] 
            return <r>{string($a)}</r>
        ') AS a
    ) _ CROSS APPLY a.nodes('r') AS x(t)
    FOR XML PATH('')
)))

From inner to outer: we unpack all the r* attributes into elements, attach a row number to them, then fold the result back into XML by lamely concatenating strings. For a finale, we transform the n attribute of recipes into r and substitute our string concatenation into the outer element.

Why is this code so terrible? Because the data model is terrible (well, and because SQL Server's implementation of XQuery is quite limited, omitting most advanced features that could simplify this). It's an abuse of XML in every way. Consider changing the attributes into child elements. Don't use concatenated element names like ALICEBACKPACKrecipes, generalize this to recipes name='ALICEBACKPACK' or suchlike. Think static names and repeating content:

<i>
  <recipes name="" value="0"></recipes>
  <recipes name="cg" value="4">
    <r>302053</r>
    <r>302084</r>
    ...
  </recipes>
  ...
  <recipes name="ALICEBACKPACK" value="1">
    <r>302112</r>
  </recipes>
  ...
</i>

This is far easier to query and process for anything that isn't a fully fledged programming language.