T-SQL xquery .modify method using a wildcard

Question

I am working in SQL Server 2014. I have created a stored procedure which does its processing thing, and at the end, takes the final query output and formats it as XML. Due to the nature of the logic in the procedure, sometimes a node must be deleted from the final XML output. Here's a sample of the XML output (for brevity I have not included the root nodes; hopefully they won't be required to answer my question):

<DALink>
    <InteractingIngredients>
        <InteractingIngredient>
            <IngredientID>1156</IngredientID>
            <IngredientDesc>Lactobacillus acidophilus</IngredientDesc>
            <HICRoot>Lactobacillus acidophilus</HICRoot>
            <PotentiallyInactive>Not necessary</PotentiallyInactive>
            <StatusCode>Live</StatusCode>
        </InteractingIngredient>
    </InteractingIngredients>
    <ActiveProductsExistenceCode>Exist</ActiveProductsExistenceCode>
    <IngredientTypeBasisCode>1</IngredientTypeBasisCode>
    <AllergenMatch>Lactobacillus acidophilus</AllergenMatch>
    <AllergenMatchType>Ingredient</AllergenMatchType>
</DALink>
<ScreenDrug>
    <DrugID>1112894</DrugID>
    <DrugConceptType>RxNorm_SemanticClinicalDr</DrugConceptType>
    <DrugDesc>RxNorm LACTOBACILLUS ACIDOPHILUS/PECTIN ORAL capsule</DrugDesc>
    <Prospective>true</Prospective>
</ScreenDrug>

In the procedure I have code that looks at the XML structure above and deletes a node when it shouldn't be there because it's easier to modify the xml than tweak the query output. Here is a sample of that code:

SET @xml_Out.modify('declare namespace ccxsd="http://schemas.foobar.com/CC/v1_3"; delete //ccxsd:InteractingIngredients[../../../ccxsd:ScreenDrug/ccxsd:DrugConceptType="OneThing"]');
SET @xml_Out.modify('declare namespace ccxsd="http://schemas.foobar.com/CC/v1_3"; delete //ccxsd:InteractingIngredients[../../../ccxsd:ScreenDrug/ccxsd:DrugConceptType="AnotherThing"]');
SET @xml_Out.modify('declare namespace ccxsd="http://schemas.foobar.com/CC/v1_3"; delete //ccxsd:InteractingIngredients[../../../ccxsd:ScreenDrug/ccxsd:DrugConceptType="SomethingElse"]');
SET @xml_Out.modify('declare namespace ccxsd="http://schemas.foobar.com/CC/v1_3"; delete //ccxsd:InteractingIngredients[../../../ccxsd:ScreenDrug/ccxsd:DrugConceptType="SomethingElseAgain"]');
SET @xml_Out.modify('declare namespace ccxsd="http://schemas.foobar.com/CC/v1_3"; delete //ccxsd:InteractingIngredients[../../../ccxsd:ScreenDrug/ccxsd:DrugConceptType="RxNorm*"]');

The final command is the one I can't figure out how to make work. All I need to do is to look for instances where the element "DrugConceptType" starts with the string "RxNorm", because there are multiple versions of the string that can possibly occur.

I have Googled and StackOverFlowed at length, but perhaps because of my inexperience in this area I didn't ask the question correctly.

Is there a relatively easy way to re-write the final .modify statement above to use a wildcard after "RxNorm"?

Answer 1

Your reduction of the root node is a problem acutally, as you are using the namespace "ccxsd" and your XML does not show this.

Anyway, better, than to write the declare namespace ... over and over, was this as first line of your statement:

;WITH XMLNAMESPACES('http://schemas.fdbhealth.com/CC/v1_3' AS ccxsd)

Well, as a .modify() is a one statement call it doesn't make such a difference...

But to your question of a wildcard

This would delete all nodes, where the Element's content starts with "RxNorm":

SET @xml.modify('delete //*[fn:substring(.,1,6)="RxNorm"]');

Be aware of missing namespaces... Cannot test it...

EDIT: A simplified working example:

You have to check this with your actual XML (with root and namespace)

DECLARE @xml_Out XML=
'<DALink>
    <InteractingIngredients>
        <InteractingIngredient>
            <IngredientID>1156</IngredientID>
            <IngredientDesc>Lactobacillus acidophilus</IngredientDesc>
            <HICRoot>Lactobacillus acidophilus</HICRoot>
            <PotentiallyInactive>Not necessary</PotentiallyInactive>
            <StatusCode>Live</StatusCode>
        </InteractingIngredient>
    </InteractingIngredients>
    <ActiveProductsExistenceCode>Exist</ActiveProductsExistenceCode>
    <IngredientTypeBasisCode>1</IngredientTypeBasisCode>
    <AllergenMatch>Lactobacillus acidophilus</AllergenMatch>
    <AllergenMatchType>Ingredient</AllergenMatchType>
</DALink>
<ScreenDrug>
    <DrugID>1112894</DrugID>
    <DrugConceptType>RxNorm_SemanticClinicalDr</DrugConceptType>
    <DrugDesc>RxNorm LACTOBACILLUS ACIDOPHILUS/PECTIN ORAL capsule</DrugDesc>
    <Prospective>true</Prospective>
</ScreenDrug>';

SET @xml_Out.modify('delete //InteractingIngredients[fn:substring((../../ScreenDrug/DrugConceptType)[1],1,6)="RxNorm"]');

SELECT @xml_Out;