CODEWITHSUNDEEP
sqlxmlmqmessagebroker
Yogus
Yogus

Reputation: 2272

How to make child as parent in xml tree

I am working on message broker. But the query I am doing is very simple and can be answered by any DB guy also .

here is the query code to read xml and getting xml output 

SET OutputRoot.XMLNSC.root.row[rowCnt].product_Info = THE (SELECT THE(SELECT C.*:Codes.*:Code AS TyrePatternCd FROM T.*:Classification[] AS C 
                    WHERE C.(XMLNSC.Attribute)Type = 'BRAND') AS product
                FROM itemMaster.*:ItemMasterHeader[] AS T );

This gives xml output like 

<root name="Product">
 <row>
  <product_Info>
   <product>
    <TyrePatternCd>002</TyrePatternCd>
   </product>    
 </row>
</root>

How can I make it like 

   <root name="Product">
     <row>
      <product_Info>
        <TyrePatternCd>002</TyrePatternCd>  
     </row>
    </root>

If I remove the AS product in query it makes column tag in tree. How can I make child as parent?

Upvotes: 1

Views: 265

Answers (1)

Shrein
Shrein

Reputation: 291

Use SELECT ITEM to omit the 'product' element, and directly assign the result.

SET OutputRoot.XMLNSC.root.row[rowCnt].product_Info = THE (SELECT ITEM THE(SELECT C.*:Codes.*:Code AS TyrePatternCd FROM T.*:Classification[] AS C 
                    WHERE C.(XMLNSC.Attribute)Type = 'BRAND')
                FROM itemMaster.*:ItemMasterHeader[] AS T );

Upvotes: 1

Related Questions