Return same node value from child node openxml sql

Question

I have following XML:-

<XML>
<ProductDetail>
   <ProductId>1</ProductId>
   <PropertyDetail>
      <PropertyKey>Size</PropertyKey>
      <PropertyValue>XXL</PropertyValue>
      <PropertyKey>ProdTaxType</PropertyKey>
      <PropertyValue>5%</PropertyValue>
      <PropertyKey>Incl/Excl</PropertyKey>
      <PropertyValue>True</PropertyValue>        
      <PropertyKey>Fit</PropertyKey>
      <PropertyValue>SLIM F/S</PropertyValue>
   </PropertyDetail>
</ProductDetail>
</XML>

But, i am getting the result as below :

ProductId   PropertyKey PropertyValue
   1           Size        XXL

I need to get the all PropertyDetail using openxml. My small query in Stored Procedure in MS SQL 2012 as below :

SELECT XML.ProductId, XML.PropertyKey, XML.PropertyValue FROM 
   OPENXML (@hDoc, '/XML/*', 2) WITH (
      ProductId INT 'ProductId',
      PropertyKey VARCHAR(200) 'PropertyDetail/PropertyKey',
      PropertyValue VARCHAR(200) 'PropertyDetail/PropertyValue'
   ) XML

The finall result would be look like as below :

ProductId   PropertyKey   PropertyValue
   1           Size          XXL
   1         ProdTaxType     5%
   1         Incl/Excl      True
   1           Fit         SLIM F/S    

Answer 1

I would try following solution:

DECLARE @x XML = N'<XML>
<ProductDetail>
   <ProductId>1</ProductId>
   <PropertyDetail>
      <PropertyKey>Size</PropertyKey>
      <PropertyValue>XXL</PropertyValue>
      <PropertyKey>ProdTaxType</PropertyKey>
      <PropertyValue>5%</PropertyValue>
      <PropertyKey>Incl/Excl</PropertyKey>
      <PropertyValue>True</PropertyValue>        
      <PropertyKey>Fit</PropertyKey>
      <PropertyValue>SLIM F/S</PropertyValue>
   </PropertyDetail>
</ProductDetail>
</XML>'

SELECT pvt.ProductId, pvt.NodeNum, pvt.[0] AS PKey, pvt.[1] AS PValue
FROM (
    SELECT  x.XmlCol.value('(ProductId/text())[1]', 'INT')          AS ProductId,
            (DENSE_RANK() OVER(ORDER BY y.XmlCol) + 1)/2            AS NodeNum,
            (DENSE_RANK() OVER(ORDER BY y.XmlCol) + 1) % 2          AS NodeType, -- 0 = PropertyKey, 1 = PropertyValue
            y.XmlCol.value('(text())[1]', 'NVARCHAR(50)')           AS NodeValue
    FROM    @x.nodes('XML/ProductDetail') x(XmlCol)
    OUTER APPLY x.XmlCol.nodes('PropertyDetail/*') y(XmlCol)
) AS src -- source
PIVOT( MAX(src.NodeValue) FOR NodeType IN ([0], [1]) ) AS pvt

Results:

ProductId NodeNum PKey        PValue
--------- ------- ----------- --------
1         1       Size        XXL
1         2       ProdTaxType 5%
1         3       Incl/Excl   True
1         4       Fit         SLIM F/S

Reference: Uniquely Identifying XML Nodes with DENSE_RANK

Online Demo

Note: I don't know if it's possible, but I would change the XML schema thus:

...
<property name="Size" value="XXL" />
<property name="ProdTaxType" value="5%" />
...

Actual XML schema is, somehow, dangerous because it relies on order of PropertyKey/PropertyValue XML elements.