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