I am trying to generate an output using Productname from below xml which will look like:

B/C/

B/D/E

demo xml:

DECLARE @myDoc xml
DECLARE @ProdID nvarchar(10)
SET @myDoc = '<Root>
<ProductDescription Productname="A" Productd="Road Bike">
<Features  Productname="B">
  <Warranty   Productname="C">1 year parts and labor</Warranty>
  <Warranty   Productname="D">
	<Warranty   Productname="E">1 year parts and labor</Warranty>
   </Warranty>

  <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
</Features>
</ProductDescription>
</Root>'

SET @ProdID =  @myDoc.value('(/Root/ProductDescription/Features/Warranty/@Productname)[1]', 'nvarchar(max)' )
SELECT @ProdID

I have a query which works perfect but  with nodes. similar way I am looking for the value column. I want a similar result from the function on "Value" column(value column is defined inside the below query) : B/D/E ....

with
CTE_xpath as (
  select
    T.C.value('local-name(.)', 'nvarchar(max)') as Name,
    T.C.query('./*') as elements,
    T.C.value('text()[1]', 'nvarchar(max)') as Value
  from @myDoc .nodes('*') as T(c)
  
  union all
  
  select
    p.Name + '/' + T.C.value('local-name(.)', 'nvarchar(max)') as Name,
    T.C.query('./*') as elements,
    T.C.value('text()[1]', 'nvarchar(max)') as Value
  from CTE_xpath as p
    cross apply p.elements.nodes('*') as T(C)
  union all
  
  select
    p.Name + '/' +
    T.C.value('local-name(..)', 'nvarchar(max)') + '/@' +
    T.C.value('local-name(.)', 'nvarchar(max)') as Name,
    null as elements,
    T.C.value('.', 'nvarchar(max)') as Value
  from CTE_xpath as p
    cross apply p.elements.nodes('*/@*') as T(C)
)
select Name, Value
from CTE_xpath
where Value is not null

• Edited by scottichrosaviakosmos 54 minutes ago