Hi,
Database : Oracle 11g
I need to pass multiple XML values for an attribute. At the moment, the below query prints the Attribute name twice.
SELECT
XMLElement("req:AttributeGroup", XMLElement ("req:GroupName", gpa.attribute_group), XMLAGG(XMLElement("req:Attribute", XMLElement ("req:Name", gpa.attribute_name),
CASE
WHEN (
gpa.attribute_name = 'XX_UK_COMMODITY_CODE'
)
THEN XMLElement ("req:CharacterValue" , XMLCData(shipping_code.uk_commodity_code))
END ) ) )
FROM
(
SELECT PIM_PRODUCT,SKU,TARIFF_CODE,COMMODITY_CODE,UK_COMMODITY_CODE
FROM SHIPPING_COMMODITY_CODES
WHERE PIM_PRODUCT = 'NTEHG'
)
SHIPPING_CODE,
g2_pff_attribute gpa
WHERE
GPA.ATTRIBUTE_GROUP = 'XX_G2_ATTRIBUTE_GROUP'
AND gpa.attribute_level = 'STYLE'
AND gpa.attribute_owner = 'G2'
AND GPA.ATTRIBUTE_ACTIVE_IND = 'Y'
GROUP BY
GPA.ATTRIBUTE_GROUP
;
Output:
<req:AttributeGroup>
<req:GroupName>XX_G2_ATTRIBUTE_GROUP</req:GroupName>
<req:Attribute>
<req:Name>XX_UK_COMMODITY_CODE</req:Name>
<req:CharacterValue>
<![CDATA[6402919001-UK]]>
</req:CharacterValue>
</req:Attribute>
<req:Attribute>
<req:Name>XX_UK_COMMODITY_CODE</req:Name>
<req:CharacterValue>
<![CDATA[6402919000-UK]]>
</req:CharacterValue>
</req:Attribute>
</req:AttributeGroup>
How do I re-structure the above query to get the CharacterValues for XX_UK_COMMODITY_CODE under one single attribute?.
Thanks
S