时间戳

首页 > 解决方案 > Oracle 11g - 将多个值传递给 XML 属性

sql - Oracle 11g - 将多个值传递给 XML 属性

问题描述

数据库：Oracle 11g

我需要为一个属性传递多个 XML 值。目前，下面的查询会打印两次属性名称。

  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
   ;

输出：

<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>

如何重新构造上述查询以在一个属性下获取 XX_UK_COMMODITY_CODE 的字符值？

谢谢

R

标签： sqloracle11g

解决方案

GROUP BY GPA.ATTRIBUTE_GROUP, GPA.ATTRIBUTE_NAME然后将元素XMLAGG从外部移动到内部：req:Attribute

SELECT XMLElement(
         "req:AttributeGroup",
         XMLElement( "req:GroupName", gpa.attribute_group ),
         XMLElement(
           "req:Attribute",
           XMLElement( "req:Name", gpa.attribute_name ),
           XMLAGG(
             CASE
             WHEN gpa.attribute_name = 'XX_UK_COMMODITY_CODE'
             THEN XMLElement ("req:CharacterValue" , XMLCData(shipping_code.uk_commodity_code))
             END
           )
         )
       ) AS xml
FROM   (
         SELECT UK_COMMODITY_CODE
         FROM   SHIPPING_COMMODITY_CODES
         WHERE  PIM_PRODUCT = 'NTEHG'                
       ) SHIPPING_CODE
       CROSS JOIN 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,
       GPA.ATTRIBUTE_NAME;

其中，对于样本数据：

CREATE TABLE SHIPPING_COMMODITY_CODES ( PIM_PRODUCT, UK_COMMODITY_CODE ) AS
SELECT 'NTEHG', '6402919001-UK' FROM DUAL UNION ALL
SELECT 'NTEHG', '6402919000-UK' FROM DUAL;

CREATE TABLE g2_pff_attribute ( attribute_group, attribute_name, attribute_level, attribute_owner, attribute_active_ind ) AS
SELECT 'XX_G2_ATTRIBUTE_GROUP', 'XX_UK_COMMODITY_CODE', 'STYLE', 'G2', 'Y' FROM DUAL;

输出：

| XML |
| :------------------------------------------------ -------------------- |
| <请求：属性组> |
| <req:GroupName>XX_G2_ATTRIBUTE_GROUP</req:GroupName> |
| <请求：属性> |
| <req:Name>XX_UK_COMMODITY_CODE</req:Name> |
| <req:CharacterValue><![CDATA[6402919001-UK]]></req:CharacterValue> |
| <req:CharacterValue><![CDATA[6402919000-UK]]></req:CharacterValue> |
| </req:属性> |
| </req:AttributeGroup> |

db<>在这里摆弄

推荐阅读