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!

You should describe what you want to do in clear terms. You example does not make it any clearer. — TT.
– TT., Commented Jul 18, 2017 at 15:24
I want to remove the <CGRecipes .... <IMITARrecipes.... all of them but I still want to keep the values inside them and rename r0....r1="ID" it has to count r0="ID" r1="ID"..... — Karuzo
– Karuzo, Commented Jul 18, 2017 at 15:28
Oh no you don't! I managed to do this in pure SQL. Pure, suffering SQL. I will have my answer posted. :-P — Jeroen Mostert
– Jeroen Mostert, Commented Jul 18, 2017 at 16:55

Jeroen Mostert · Accepted Answer · 2017-07-18 16:55:54Z

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.

Even if the OP wanted to close this, your answer (and your hints!) are worth an upvote :-D

Collectives™ on Stack Overflow

How to replace XML queries in SQL Server?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related