Replace string using pattern in XML string in Sql Server

Question

I would like to replace span tags from XML string using regex from multiple records in a column of type string.

Looking for how to remove starting span tags for Ex: "<span class="TAGGED_ITEM " id="c1_ae1">"

Will be able to replace "</span>" with Empty string.

Here is the Input.

    <Item title="1234" xmlns="http://www.imsglobal.org/xsd/imsqti_v2p2">
        <ItemBody>
        <div class="item_text">
                        <div>
                            <span class="TAGGED_ITEM " id="c1_ae1">This is a map on a grid.</span>
                            <span class="TAGGED_ITEM " id="c1_ae2"> It shows a car.</span>
                        </div>
                            <span class="TAGGED_ITEM " id="c1_ae3"> It shows a car on Road.</span>
                    </div>
        </ItemBody>
    </Item>

Here is output.

  <Item title="1234" xmlns="http://www.imsglobal.org/xsd/imsqti_v2p2">
        <ItemBody>
        <div class="item_text">
                        <div>
                            This is a map on a grid.
                             It shows a car.
                        </div>
                           It shows a car on Road.
                    </div>
        </ItemBody>
    </Item>

This may help you: http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql — Khamyl, Mar 09 '16 at 14:16
Have a look here.. http://stackoverflow.com/a/13241658/175063 — Leptonator, Mar 09 '16 at 14:20

score 1 · Accepted Answer · answered Mar 09 '16 at 14:51

1

Use this regex to replace span tag with empty string

<span.+?>|<\/span>

For your Input the above regex select span tag :

answered Mar 09 '16 at 14:51

Ashraful Islam

12,470
3
32
53

Leptonator · Answer 2 · 2016-03-09T15:01:44.010

1

If you really want to replace your span tags, this should do it for you.

<span class[^>]*>|<\/span>

have a look here: DEMO

edited Mar 09 '16 at 15:01

answered Mar 09 '16 at 14:56

Leptonator

3,379
2
38
51

Replace string using pattern in XML string in Sql Server

2 Answers2