select same child node from openxml sql stored procedure

Question

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.

Tag the dbms you're using. (Most products have their own versions of stored procedures.) — jarlh
– jarlh, Commented Aug 4, 2017 at 7:40
Aren't you missing calling EXEC sp_xml_preparedocument @idoc OUTPUT, @doc; in your sample? ` — Vojtěch Dohnal
– Vojtěch Dohnal, Commented Aug 4, 2017 at 9:05

Vojtěch Dohnal · Accepted Answer · 2017-08-04 09:17:44Z

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.

Collectives™ on Stack Overflow

select same child node from openxml sql stored procedure

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related