CODEWITHSUNDEEP
sqlsql-serverstored-proceduresopenxml
Yogesh Sharma
Yogesh Sharma

Reputation: 50163

select same child node from openxml sql stored procedure

I have one XML String as below :

<XML>
   <ProductDetail>
        <Barcode>170604658</Barcode>
       <PropertyDetail>
             <PropertyKey>Size</PropertyKey>
             <PropertyValue>XXL</PropertyValue>
             <PropertyKey>ProdTaxType</PropertyKey>
             <PropertyValue>5%</PropertyValue>
       </PropertyDetail>
   </ProductDetail>
</XML>

i m reading the XML Data via below small query in sp:

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

BUT , FOR above i m getting one row data as below :

PropertyKey PropertyValue
    Size    XXL

BUT , as per XML Defined i want to get the data as per below :

PropertyKey     PropertyValue
 Size              XXL
 ProdTaxType       5%

FOR the same chile node name i am getting only one record from xml string. So, please suggest in above sp.

Upvotes: 1

Views: 314

Answers (1)

Vojtěch Dohnal
Vojtěch Dohnal

Reputation: 8104

Because your XML is not formed well, it should be like this:

DECLARE @hdoc varchar(max) = '<XML>
      <ProductDetail>
        <Barcode>150604658</Barcode>
       <PropertyDetail>
                     <PropertyKey>Size</PropertyKey>
                     <PropertyValue>XXL</PropertyValue>
       </PropertyDetail>
       <PropertyDetail>
                    <PropertyKey>ProdTaxType</PropertyKey>
                    <PropertyValue>5%</PropertyValue>
       </PropertyDetail>
   </ProductDetail>
</XML>'

In your example the PropertyDetail has duplicate attributes.

It could be also re-written like this:

DECLARE @hdoc varchar(max) = '<XML>
      <ProductDetail Barcode="150604658">
       <PropertyDetail PropertyKey="Size" PropertyValue="XXL" />
       <PropertyDetail PropertyKey="ProdTaxType" PropertyValue="5%" />
     </ProductDetail>
</XML>'

Now you would not think of writing:

<PropertyDetail PropertyKey="Size" PropertyValue="XXL" 
                PropertyKey="ProdTaxType" PropertyValue="5%" />

but the meaning is the same as the XML you have - and duplicate attributes are not allowed.

Upvotes: 2

Related Questions