LASTMOVE loop is not working

Question

my xml looks like

- <ItemMaster> 
     - <ItemMasterHeader> 
        + <ItemID> 
        + <ItemStatus> 
        + <UserArea> 
         - <Classification Type="HOMOLOGATION CLASS"> 
           - <Codes> 
             <Code>E</Code> 
           </Codes> 
         </Classification> 
       + <Classification Type="LP"> 
       + <Classification> 
        - <Classification Type="BRAND"> 
          - <Codes> 
              <Code>002</Code> 
          </Codes> 
        </Classification> 

Yhe full xml is here http://www.speedyshare.com/MgCCA/download/ItemMaster-2.xml

I need to fetch the value of Classification with attribute TYPE= "BRAND" but with below code, it only fetchs the classification with attribute TYPE = "HOMOLOGATION CLASS" which I dont want since I am calling for "BRAND". I tried to apply LASTMOVE but dosent work. Please tell me where I am wrong.

I have to fetch other values also like codes inside the type -"LP" also.

DECLARE rResource REFERENCE TO InputRoot.XMLNSC.*:SyncItemMaster.*:DataArea.*:ItemMaster.*:ItemMasterHeader[1]; 
      SET rowCnt = rowCnt+1;       
      DECLARE LineCount INTEGER 1; 

      WHILE LASTMOVE(rResource) = TRUE DO    
      SET OutputRoot.XMLNSC.root.row[rowCnt].product_Info.TyreBrandCd = THE (SELECT  ITEM FIELDVALUE(T) FROM itemMaster.*:ItemMasterHeader[LineCount].*:Classification.*:Codes.*:Code AS T WHERE FIELDVALUE(itemMaster.*:ItemMasterHeader[LineCount].*:Classification.(XMLNSC.Attribute)Type) = 'BRAND'); 
            SET LineCount = LineCount + 1; 
      MOVE rResource NEXTSIBLING REPEAT TYPE NAME; 
       END WHILE;             
   RETURN TRUE; 
   END;

Thanks

TRIED with below suggested code

Here are trace logs

2013-05-10 18:32:27.218385 7732 UserTrace BIP2537I: Node 'WMB_9D1_PROD_SUB00_001.9D1_PROD': Executing statement ''SET temp = THE (SELECT T.Classification AS :Classification FROM myref AS T WHERE FIELDVALUE(T.Classification.(XMLNSC.Attribute)Type) = 'BRAND');'' at ('.WMB_9D1_PROD_SUB00_001.Main', '22.3').
2013-05-10 18:32:27.218393 7732 UserTrace BIP2538I: Node 'WMB_9D1_PROD_SUB00_001.9D1_PROD': Evaluating expression ''THE (SELECT T.Classification AS :Classification FROM myref AS T WHERE FIELDVALUE(T.Classification.(XMLNSC.Attribute)Type) = 'BRAND')'' at ('.WMB_9D1_PROD_SUB00_001.Main', '22.14').
2013-05-10 18:32:27.218400 7732 UserTrace BIP2572W: Node: 'WMB_9D1_PROD_SUB00_001.9D1_PROD': ('.WMB_9D1_PROD_SUB00_001.Main', '22.14') : Finding one and only SELECT result.
2013-05-10 18:32:27.218427 7732 UserTrace BIP2539I: Node 'WMB_9D1_PROD_SUB00_001.9D1_PROD': Evaluating expression ''myref'' at ('.WMB_9D1_PROD_SUB00_001.Main', '22.48'). This resolved to ''myref''. The result was ''ROW... Root Element Type=16777216 NameSpace='' Name='ItemMasterHeader' Value=NULL''.
2013-05-10 18:32:27.218437 7732 UserTrace BIP2539I: Node 'WMB_9D1_PROD_SUB00_001.9D1_PROD': Evaluating expression ''XMLNSC.Attribute'' at ('.WMB_9D1_PROD_SUB00_001.Main', '22.94'). This resolved to ''XMLNSC.Attribute''. The result was ''1095266992384''.
2013-05-10 18:32:27.218446 7732 UserTrace BIP2540I: Node 'WMB_9D1_PROD_SUB00_001.9D1_PROD': Finished evaluating expression ''FIELDVALUE(T.Classification.(XMLNSC.Attribute)Type)'' at ('.WMB_9D1_PROD_SUB00_001.Main', '22.65'). The result was '''HOMOLOGATION CLASS'''.
2013-05-10 18:32:27.218454 7732 UserTrace BIP2539I: Node 'WMB_9D1_PROD_SUB00_001.9D1_PROD': Evaluating expression ''FIELDVALUE(T.Classification.(XMLNSC.Attribute)Type) = 'BRAND''' at ('.WMB_9D1_PROD_SUB00_001.Main', '22.117'). This resolved to '''HOMOLOGATION CLASS' = 'BRAND'''. The result was ''FALSE''.
2013-05-10 18:32:27.218461 7732 UserTrace BIP2569W: Node 'WMB_9D1_PROD_SUB00_001.9D1_PROD': ('.WMB_9D1_PROD_SUB00_001.Main', '22.14') : WHERE clause evaluated to false or unknown. Iterating FROM clause.
2013-05-10 18:32:27.218469 7732 UserTrace BIP2570W: Node 'WMB_9D1_PROD_SUB00_001.9D1_PROD': ('.WMB_9D1_PROD_SUB00_001.Main', '22.14') : There were no items in the FROM clause satisfying the WHERE clause.
2013-05-10 18:32:27.218503 7732 UserTrace BIP2567I: Node 'WMB_9D1_PROD_SUB00_001.9D1_PROD': Assigning NULL to ''temp'', thus deleting it.

Answer 1

I'm not sure what's the kind of mapping you're looking for. Assuming that what you want is for the (unique) 'Code', on the 'Classification' with the right attribute on each 'ItemMasterHeader', to be present in the output inside separate 'row' folders, here's the code:

CREATE PROCEDURE ExtractTyreCodes() BEGIN
        DECLARE rOutput REFERENCE TO OutputRoot;
        DECLARE rResource REFERENCE TO InputRoot.XMLNSC.*:SyncItemMaster.*:DataArea.*:ItemMaster;
        CREATE FIELD OutputRoot.XMLNSC.root AS rOutput;
        IF LASTMOVE(rResource) THEN 
        SET rOutput.row[] = SELECT 
                THE(SELECT C.*:Codes.*:Code AS TyreBrand
                    FROM T.*:Classification[] AS C 
                    WHERE C.(XMLNSC.Attribute)Type = 'BRAND') AS product_Info
                FROM rResource.*:ItemMasterHeader[] AS T;
        END IF;
END;

Starting from this message:

<SyncItemMaster>
  <DataArea>
    <ItemMaster>
      <ItemMasterHeader>
        <ItemID/>
        <ItemStatus/>
        <UserArea/>
        <Classification Type="HOMOLOGATION CLASS">
          <Codes>
            <Code>E</Code>
          </Codes>
        </Classification>
        <Classification Type="LP"/>
        <Classification/>
        <Classification Type="BRAND">
          <Codes>
            <Code>002</Code>
          </Codes>
        </Classification>
      </ItemMasterHeader>
      <ItemMasterHeader>
        <ItemID/>
        <ItemStatus/>
        <UserArea/>
        <Classification Type="HOMOLOGATION CLASS">
          <Codes>
            <Code>F</Code>
          </Codes>
        </Classification>
        <Classification Type="LP"/>
        <Classification/>
        <Classification Type="BRAND">
          <Codes>
            <Code>005</Code>
          </Codes>
        </Classification>
      </ItemMasterHeader>
    </ItemMaster>
  </DataArea>
</SyncItemMaster>

You get this message:

<root>
  <row>
    <product_Info>
      <TyreBrand>002</TyreBrand>
    </product_Info>
  </row>
  <row>
    <product_Info>
      <TyreBrand>005</TyreBrand>
    </product_Info>
  </row>
</root>

This generates a 'row' folder for each 'ItemMasterHeader', puts inside of each a 'product_Info' folder, and inside of that one puts the code from the 'Classification' of (Attribute) 'Type' = 'BRAND'.

Hope this helps. Regards,

Answer 2

Try this:

declare temp ROW;

SET temp = THE (SELECT T.Classification FROM rResource AS T WHERE FIELDVALUE(T.Classification.(XMLNSC.Attribute)Type) = 'BRAND');

OutputRoot.XMLNSC.root.row[rowCnt].product_Info.TyreBrandCd = temp.code;