There are few ways of doing this, but can you tell us more about the data, how big are the sets, more details about the structure etc. so we can find the best solution.

😎

One is to retrieve the element-attribute-value as an EAV type set, here is a quick demo.

USE tempdb;

GO

SET NOCOUNT ON;

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">1 year parts and labor</Warranty>

<Warranty Productname="E">1 year parts and labor</Warranty>

<Maintenance>3 year parts and labor extended maintenance is available</Maintenance>

</Features>

</ProductDescription>

</Root>'

/* EAV type parsing */

SELECT

ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS EAV_RID

,DOC.DATA.value('local-name(../.)','varchar(50)') AS ELEMENT_NAME

,DOC.DATA.value('local-name(.)','varchar(50)') AS ATTRIBUTE_NAME

,DOC.DATA.value('.','varchar(50)') AS ATTRIBUTE_VALUE

from @myDoc.nodes('//*/@*') AS DOC(DATA);

Results

EAV_RID ELEMENT_NAME ATTRIBUTE_NAME ATTRIBUTE_VALUE

-------- -------------------- --------------- ----------------

1 ProductDescription Productname A

2 ProductDescription Productd Road Bike

3 Features Productname B

4 Warranty Productname C

5 Warranty Productname D

6 Warranty Productname E