How to create a SQL function to parse multiple xml formatted strings?

I am trying to parse a price from a price column in SQL. I have already create a function that will parse the price but it only works if “all” price strings are the same on all items. I realize now that I need a more robust function that can parse out multiple price configuration.

Here is my simple SQL query

I.[NAME] AS 'ITEM NAME'
,DBO.parsePrice(I.PRICING) AS 'ITEM PRICE'
FROM ITEM I
order by 'ITEM NAME'

Here is my function DBO.parsePrice

RETURNS decimal(8,2) AS 
BEGIN
    RETURN CAST(@in AS XML).value(N'(/prices[@type="Level"]/item[@level="A"])[1]/@price', N'DECIMAL(14,2)');
END

Here is the XML string with just one price level.

<prices type="Level"><item level="A" price="10.00" /></prices>

The function will output 10.00 from the string above.

Here is another example with a max of 6 different price levels

<prices type="Level"><item level="A" price="10.00" /><item level="B" price="11.00" /><item level="C" price="12.00" /><item level="D" price="13.00" /><item level="E" price="14.00" /><item level="F" price="15.00" /></prices>

My function was only designed to parse out price level (A). To make things even more complicated the price string can also be configured with different order types rather than price levels.

<prices type="OrderType"><item level="DineIn" price="7" /><item level="TakeOut" price="8.00" /><item level="Delivery" price="" /><item level="Sale" price="" /></prices>

The new function needs to handle all these different situations. Ideally, I would like to have separate columns for each price break rather than a field that has the prices comma separated.

Any help would be greatly appreciated

Regards,

JC

The above SQL query was just an example for posting the question. My actual query is pulling data from other tables using left joins.

SELECT
I.[NAME] AS 'ITEM NAME'
,DBO.parsePrice(I.PRICING) AS 'ITEM PRICE'
,ML.NAME AS 'MODIFIER LIST NAME'
,MG.NAME AS 'MODIFIER GROUP NAME'
,M.NAME AS 'MODIFIER NAME'
,M.UPCHARGE_EXPRESSION AS UPCHARGE
FROM ITEM I
LEFT JOIN [dbo].[ITEM_MODIFIER] IM ON IM.ITEM_RECORD_KEY = I.RECORD_KEY
LEFT JOIN [dbo].[MODIFIER_LIST] ML ON ML.RECORD_KEY = IM.MODIFIER_LIST_RECORD_KEY
LEFT JOIN [dbo].[MODIFIER_GROUP] MG ON MG.MODIFIER_LIST_RECORD_KEY = ML.RECORD_KEY
LEFT JOIN [dbo].[MODIFIER] M ON M.MODIFIER_GROUP_RECORD_KEY = MG.RECORD_KEY
order by 'ITEM NAME'

Answer

You can create a TVF returning (level, price)

create function DBO.parsePrice(@x xml)
returns table
return
select p.n.value('@level', 'nvarchar(100)') level, p.n.value('@price', 'decimal(9,2)') price
from @x.nodes('prices/item') p(n)

and use it in the query

SELECT
I.[NAME] AS 'ITEM NAME'
, p.*
,ML.NAME AS 'MODIFIER LIST NAME'
,MG.NAME AS 'MODIFIER GROUP NAME'
,M.NAME AS 'MODIFIER NAME'
,M.UPCHARGE_EXPRESSION AS UPCHARGE
FROM ITEM I
LEFT JOIN [dbo].[ITEM_MODIFIER] IM ON IM.ITEM_RECORD_KEY = I.RECORD_KEY
LEFT JOIN [dbo].[MODIFIER_LIST] ML ON ML.RECORD_KEY = IM.MODIFIER_LIST_RECORD_KEY
LEFT JOIN [dbo].[MODIFIER_GROUP] MG ON MG.MODIFIER_LIST_RECORD_KEY = ML.RECORD_KEY
LEFT JOIN [dbo].[MODIFIER] M ON M.MODIFIER_GROUP_RECORD_KEY = MG.RECORD_KEY
CROSS APPLY DBO.parsePrice(I.PRICING) p
order by 'ITEM NAME', p.Level

EDIT

Following OP remarks this is a version of the TVF which returns 0 for @price which is not numeric

create function DBO.parsePrice(@x xml)
returns table
return
select level, coalesce(try_cast(price as decimal(9,2)),0) price
from (
   select p.n.value('@level', 'varchar(100)') level, p.n.value('@price', 'nvarchar(50)') price
   from @x.nodes('prices/item') p(n)
) t