Oracle XML

-- create the tables
CREATE TABLE customers (
  customer_id INTEGER CONSTRAINT customers_pk PRIMARY KEY,
  first_name VARCHAR2(10) NOT NULL,
  last_name VARCHAR2(10) NOT NULL,
  dob DATE,
  phone VARCHAR2(12),
  type number
);

-- insert sample data into customers table
INSERT INTO customers (
  customer_id, first_name, last_name, dob, phone, type
) VALUES (
  1, 'John', 'Brown', '01-JAN-1965', '800-555-1211', 0
);

INSERT INTO customers (
  customer_id, first_name, last_name, dob, phone, type
) VALUES (
  2, 'Cynthia', 'Green', '05-FEB-1968', '800-555-1212', 0
);

INSERT INTO customers (
  customer_id, first_name, last_name, dob, phone, type
) VALUES (
  3, 'Steve', 'White', '16-MAR-1971', '800-555-1213', 1
);

INSERT INTO customers (
  customer_id, first_name, last_name, dob, phone, type
) VALUES (
  4, 'Gail', 'Black', NULL, '800-555-1214', 1
);

INSERT INTO customers (
  customer_id, first_name, last_name, dob, phone, type
) VALUES (
  5, 'Doreen', 'Blue', '20-MAY-1970', NULL, 1
);

-- commit the transaction
COMMIT;

SELECT XMLELEMENT(
    "customer",
    XMLELEMENT("customer_id", customer_id),
    XMLELEMENT("name", first_name || ' ' || last_name)
  ).GETSTRINGVAL() AS xml_customers
  FROM customers
 WHERE customer_id IN (1, 2);

SELECT XMLELEMENT(
    "customer",
    XMLATTRIBUTES(
      customer_id AS "id",
      first_name || ' ' || last_name AS "name",
      TO_CHAR(dob, 'MM/DD/YYYY') AS "dob"
    )
  ).GETSTRINGVAL() AS xml_customers
  FROM customers
 WHERE customer_id IN (1, 2);

SELECT XMLELEMENT(
    "customer",
    XMLFOREST(
      customer_id AS "id",
      first_name || ' ' || last_name AS "name",
      TO_CHAR(dob, 'MM/DD/YYYY') AS "dob",
      '' AS 'empty'
    )
  ).GETSTRINGVAL() AS xml_customers
  FROM customers
 WHERE customer_id IN (1, 2);

SELECT XMLELEMENT(
    "customer_in_group",
    XMLATTRIBUTES(type as "type"),
    XMLAGG(
      XMLELEMENT("name", first_name || ' ' || last_name)
    )
  ).GETSTRINGVAL() AS xml_customers
  FROM customers
 GROUP BY type;

SELECT XMLELEMENT(
    "customer",
    XMLCOLATTVAL(
      customer_id AS "id",
      dob as "dob"
    )
  ).GETSTRINGVAL() AS xml_customers
  FROM customers
 WHERE customer_id in (1, 2);

SELECT XMLCONCAT(
    XMLELEMENT("name", first_name || ' ' || last_name),
    XMLELEMENT("phone", phone)
  ).GETSTRINGVAL() AS xml_customers
  FROM customers
 WHERE customer_id in (1, 2);

SELECT XMLPARSE(
    CONTENT
    '<customer><customer_id>1</customer_id><name>John Brown</name></customer>'
    WELLFORMED
  ).GETSTRINGVAL() AS xml_customer
 FROM dual;

SELECT XMLPI(
  NAME "xml-stylesheet",
  'type="text/css" href="example.css"'
).GETSTRINGVAL() AS xml_sheet_pi
FROM dual;

SELECT XMLCOMMENT(
  'An example XML comment'
).GETSTRINGVAL() AS xml_comment
FROM dual;

SELECT XMLELEMENT(
    "customer",
    XMLELEMENT("customer_id", customer_id),
    XMLELEMENT("name", XMLCDATA(first_name || ' ' || last_name))
  ).GETSTRINGVAL() AS xml_customers
  FROM customers
 WHERE customer_id IN (1, 2);

SELECT VALUE(list_of_values).GETSTRINGVAL() order_values
  FROM TABLE(
    XMLSEQUENCE(
      EXTRACT(
        XMLType('<A><B>PLACED</B><B>PENDING</B><B>SHIPPED</B></A>'),
        '/A/B'
      )
    )
  ) list_of_values;

CREATE TABLE xml_tab OF XMLTYPE;

DECLARE
  l_xmltype XMLTYPE;
BEGIN
  SELECT sys_xmlagg(
           xmlelement(
             "EMP",
             xmlforest(e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm)
           )
         )
  INTO   l_xmltype
  FROM   scott.emp e;

  INSERT INTO xml_tab VALUES (l_xmltype);
  COMMIT;
END;
/

SELECT extract(value(d), '//EMPNO/text()').getNumberVal() AS empno,
       extract(value(d), '//ENAME/text()').getStringVal() AS ename,
       extract(value(d), '//JOB/text()').getStringVal() AS job,
       extract(value(d), '//MGR/text()').getNumberVal() AS mgr,
       extract(value(d), '//HIREDATE/text()').getStringVal() AS hiredate,
       extract(value(d), '//SAL/text()').getNumberVal() AS sal
FROM   xml_tab x,
       table(xmlsequence(extract(value(x), '/ROWSET/EMP'))) d;

SELECT XMLSERIALIZE(
    CONTENT XMLType('<order_status>SHIPPED</order_status>')
) AS xml_order_status
FROM DUAL;

SELECT XMLSERIALIZE(
    CONTENT XMLType('<order_status>SHIPPED</order_status>') AS BLOB
) AS xml_order_status
FROM DUAL;

SELECT XMLQUERY(
    '(1, 2 + 5, "D", 100 to 102, <A>text</A>)'
    RETURNING CONTENT
) AS xml_output
FROM DUAL;

CREATE PROCEDURE create_xml_resources AS
  v_result BOOLEAN;

  -- create string containing XML for products
  v_products VARCHAR2(300):=
    '<?xml version="1.0"?>' ||
    '<products>' ||
      '<product product_id="1" product_type_id="1" name="Modern Science" price="19.95"/>' ||
      '<product product_id="2" product_type_id="1" name="Chemistry" price="30"/>' ||
      '<product product_id="3" product_type_id="2" name="Supernova" price="25.99"/>' ||
    '</products>';

  -- create string containing XML for product types
  v_product_types VARCHAR2(300):=
    '<?xml version="1.0"?>' ||
    '<product_types>' ||
      '<product_type product_type_id="1" name="Book"/>' ||
      '<product_type product_type_id="2" name="Video"/>' ||
    '</product_types>';
BEGIN
  -- create resource for products
  v_result := DBMS_XDB.CREATERESOURCE('/public/products.xml', v_products);

  -- create resource for product types
  v_result := DBMS_XDB.CREATERESOURCE('/public/product_types.xml', v_product_types);

END create_xml_resources;
/

CALL create_xml_resources();

SELECT XMLQUERY(
  'for $product in doc("/public/products.xml")/products/product
   return <product name="{$product/@name}"/>'
   RETURNING CONTENT
).GETSTRINGVAL() AS xml_products
FROM dual;

SELECT XMLQUERY(
  'for $product in doc("/public/products.xml")/products/product
   let $product_type := doc("/public/product_types.xml")//product_type[@product_type_id = $product/@product_type_id]/@name
   where $product/@price > 20
   order by $product/@product_id
   return <product name="{$product/@name}" product_type="{$product_type}"/>'
   RETURNING CONTENT
).GETSTRINGVAL() AS xml_query_results
FROM dual;

SELECT XMLQUERY(
  'for $product_type in doc("/public/product_types.xml")/product_types/product_type
   let $product := doc("/public/products.xml")//product[@product_type_id = $product_type/@product_type_id]
   return
     <product
       name="{$product_type/@name}"
       num_products="{count($product)}"
       average_price="{xs:integer(avg($product/@price))}"
       />'
   RETURNING CONTENT
).GETSTRINGVAL() AS xml_query_results
FROM dual;

create table pivot_test (
  id            number,
  customer_id   number,
  product_code  varchar2(5),
  quantity      number
);

insert into pivot_test values (1, 1, 'A', 10);
insert into pivot_test values (2, 1, 'B', 20);
insert into pivot_test values (3, 1, 'C', 30);
insert into pivot_test values (4, 2, 'A', 40);
insert into pivot_test values (5, 2, 'C', 50);
insert into pivot_test values (6, 3, 'A', 60);
insert into pivot_test values (7, 3, 'B', 70);
insert into pivot_test values (8, 3, 'C', 80);
insert into pivot_test values (9, 3, 'D', 90);
insert into pivot_test values (10, 4, 'A', 100);
commit;

select *
   from (select product_code, quantity
           from pivot_test)
pivot xml (sum(quantity) as sum_quantity for (product_code) in (select distinct product_code 
                                                                  from pivot_test
                                                                 where id < 10));

select *
  from (select product_code, quantity
          from pivot_test)
pivot xml (sum(quantity) as sum_quantity for (product_code) in (any));

-- create the types
CREATE TYPE t_product AS OBJECT (
  product_id INTEGER,
  name VARCHAR2(15),
  quantity INTEGER
);
/

CREATE TYPE t_nested_table_product AS TABLE OF t_product;
/

-- create the table
CREATE TABLE purchase_order (
  purchase_order_id INTEGER CONSTRAINT purchase_order_pk PRIMARY KEY,
  customer_order_id INTEGER,
  order_date DATE,
  customer_name VARCHAR2(25),
  street VARCHAR2(15),
  city VARCHAR2(15),
  state VARCHAR2(2),
  zip VARCHAR2(5),
  phone_number VARCHAR2(12),
  products t_nested_table_product,
  xml_purchase_order XMLType
)
NESTED TABLE products
STORE AS nested_products;

-- create the directory (you may need to modify this line)
-- then copy purchase_order.xml to the directory and grant permission `chmod '/usr/tmp/edi/purchase_order.xml' 777`
CREATE OR REPLACE DIRECTORY XML_FILES_DIR AS '/usr/tmp/edi';
SELECT * FROM ALL_DIRECTORIES WHERE DIRECTORY_NAME = 'XML_FILES_DIR';

-- add a row to the table
INSERT INTO purchase_order (
  purchase_order_id,
  xml_purchase_order
) VALUES (
  1,
  XMLType(
    BFILENAME('XML_FILES_DIR', 'purchase_order.xml'),  -- 指向外部文件的指针
    NLS_CHARSET_ID('AL32UTF8') -- 标准 UTF-8 编码
  )
);

SELECT * FROM purchase_order;

SELECT
    EXTRACT(xml_purchase_order, '/purchase_order/customer_order_id') cust_order_id,
    EXTRACT(xml_purchase_order, '/purchase_order/order_date') order_date,
    EXTRACT(xml_purchase_order, '/purchase_order/customer_name') cust_name,
    EXTRACT(xml_purchase_order, '/purchase_order/phone_number') phone_number
  FROM purchase_order
 WHERE purchase_order_id = 1;

SELECT
    EXTRACTVALUE(xml_purchase_order, '/purchase_order/customer_order_id') cust_order_id,
    EXTRACTVALUE(xml_purchase_order, '/purchase_order/order_date') order_date,
    EXTRACTVALUE(xml_purchase_order, '/purchase_order/customer_name') cust_name,
    EXTRACTVALUE(xml_purchase_order, '/purchase_order/phone_number') phone_number
  FROM purchase_order
 WHERE purchase_order_id = 1;

SELECT
    EXTRACT(xml_purchase_order, '/purchase_order//products').GETSTRINGVAL() xml_products
  FROM purchase_order
 WHERE purchase_order_id = 1;

SELECT
    EXTRACT(xml_purchase_order, '/purchase_order/products/product[2]').GETSTRINGVAL() xml_product
  FROM purchase_order
 WHERE purchase_order_id = 1;

SELECT
   EXTRACT(xml_purchase_order, '/purchase_order/products/product[name="Supernova video"]').GETSTRINGVAL() xml_product
 FROM purchase_order
WHERE purchase_order_id = 1;

 SELECT 'EXISTS'
  FROM purchase_order
 WHERE purchase_order_id = 1
   AND EXISTSNODE(
     xml_purchase_order,
     '/purchase_order/products/product[product_id=1]') = 1;

--EXISTS

SELECT product.column_value--.GETSTRINGVAL()
  FROM TABLE(
    SELECT
       XMLSEQUENCE(EXTRACT(xml_purchase_order, '/purchase_order//product'))
      FROM purchase_order
     WHERE purchase_order_id = 1) product;

SELECT
    EXTRACTVALUE(product.column_value, '/product/product_id') AS product_id,
    EXTRACTVALUE(product.column_value, '/product/name') AS name,
    EXTRACTVALUE(product.column_value, '/product/quantity') AS quantity
  FROM TABLE(
    SELECT
       XMLSEQUENCE(EXTRACT(xml_purchase_order, '/purchase_order//product'))
      FROM purchase_order
     WHERE purchase_order_id = 1) product;

CREATE PROCEDURE update_purchase_order(
  p_purchase_order_id IN purchase_order.purchase_order_id%TYPE
) AS
  v_count INTEGER := 1;

  -- declare a nested table to store products
  v_nested_table_products t_nested_table_product :=
    t_nested_table_product();

  -- declare a type to represent a product record
  TYPE t_product_record IS RECORD (
    product_id INTEGER,
    name VARCHAR2(15),
    quantity INTEGER
  );

  -- declare a REF CURSOR type to point to product records
  TYPE t_product_cursor IS REF CURSOR RETURN t_product_record;

  -- declare a cursor
  v_product_cursor t_product_cursor;

  -- declare a variable to store a product record
  v_product t_product_record;
BEGIN
  -- open v_product_cursor to read the product_id, name, and quantity for
  -- each product stored in the XML of the xml_purchase_order column
  -- in the purchase_order table
  OPEN v_product_cursor FOR
  SELECT
    EXTRACTVALUE(product.COLUMN_VALUE, '/product/product_id')
      AS product_id,
    EXTRACTVALUE(product.COLUMN_VALUE, '/product/name') AS name,
    EXTRACTVALUE(product.COLUMN_VALUE, '/product/quantity') AS quantity
  FROM TABLE(
    SELECT
      XMLSEQUENCE(EXTRACT(xml_purchase_order, '/purchase_order//product'))
    FROM purchase_order
    WHERE purchase_order_id = p_purchase_order_id
  ) product;

  -- loop over the contents of v_product_cursor
  LOOP
    -- fetch the product records from v_product_cursor and exit when there
    -- are no more records found
    FETCH v_product_cursor INTO v_product;
    EXIT WHEN v_product_cursor%NOTFOUND;

    -- extend v_nested_table_products so that a product can be stored in it
    v_nested_table_products.EXTEND;

    -- create a new product and store it in v_nested_table_products
    v_nested_table_products(v_count) :=
      t_product(v_product.product_id, v_product.name, v_product.quantity);

    -- display the new product stored in v_nested_table_products
    DBMS_OUTPUT.PUT_LINE('product_id = ' ||
      v_nested_table_products(v_count).product_id);
    DBMS_OUTPUT.PUT_LINE('name = ' ||
      v_nested_table_products(v_count).name);
    DBMS_OUTPUT.PUT_LINE('quantity = ' ||
      v_nested_table_products(v_count).quantity);

    -- increment v_count ready for the next iteration of the loop
    v_count := v_count + 1;
  END LOOP;

  -- close v_product_cursor
  CLOSE v_product_cursor;

  -- update the purchase_order table using the values extracted from the
  -- XML stored in the xml_purchase_order column (the products nested
  -- table is set to v_nested_table_products already populated by the
  -- previous loop)
  UPDATE purchase_order
  SET
    customer_order_id =
      EXTRACTVALUE(xml_purchase_order,
        '/purchase_order/customer_order_id'),
    order_date =
      TO_DATE(EXTRACTVALUE(xml_purchase_order,
        '/purchase_order/order_date'), 'YYYY-MM-DD'),
    customer_name =
      EXTRACTVALUE(xml_purchase_order, '/purchase_order/customer_name'),
    street =
      EXTRACTVALUE(xml_purchase_order, '/purchase_order/street'),
    city =
      EXTRACTVALUE(xml_purchase_order, '/purchase_order/city'),
    state =
      EXTRACTVALUE(xml_purchase_order, '/purchase_order/state'),
    zip =
      EXTRACTVALUE(xml_purchase_order, '/purchase_order/zip'),
    phone_number =
      EXTRACTVALUE(xml_purchase_order, '/purchase_order/phone_number'),
    products = v_nested_table_products
  WHERE purchase_order_id = p_purchase_order_id;

  -- commit the transaction
  COMMIT;
END update_purchase_order;
/


CALL update_purchase_order();

select yeh.po_number,
       XMLELEMENT("table",
         XMLCONCAT(
           XMLELEMENT("tr", 
             XMLELEMENT("th", 'EDI Type'),
             XMLELEMENT("th", 'PO Number'),
             XMLELEMENT("th", 'PO Release Number'),
             XMLELEMENT("th", 'PO Date'),
             XMLELEMENT("th", 'Ship to Code'),
             XMLELEMENT("th", 'Ship to Name'),
             XMLELEMENT("th", 'Sold to Code'),
             XMLELEMENT("th", 'Sold to Name')
           ),
           XMLAGG( 
             XMLELEMENT("tr", 
               XMLELEMENT("td", yeh.edi_type),
               XMLELEMENT("td", yeh.po_number),
               XMLELEMENT("td", yeh.po_release_number),
               XMLELEMENT("td", yeh.po_date),
               XMLELEMENT("td", yea_ship.code),
               XMLELEMENT("td", yea_ship.name),
               XMLELEMENT("td", yea_sold.code),
               XMLELEMENT("td", yea_sold.name)
             )
           )
         )
       ).getStringVal() as table_str
  from ysc_edi_inbound_header  yeh,
       ysc_edi_inbound_address  yea_ship,
       ysc_edi_inbound_address  yea_sold
 where yeh.header_id = yea_ship.header_id
   and yeh.header_id = yea_sold.header_id
   and yea_ship.identifier_code = 'ST'
   and yea_sold.identifier_code = 'BY'
   and yeh.header_id = 1
 group by yeh.po_number;

select item.*
  from som_edi_customer_source ses,
       xmltable(
         '/Pip3A4PurchaseOrderRequest/PurchaseOrder/ProductLineItem'
          passing xmltype(ses.memo)  --memo type is clob, if memo is xmltype can direct use passing ses.memo
          columns
            cust_name varchar2(1000) path 'CustomerInformation/businessName',
            cust_po_number varchar2(1000) path 'PartnerBusiness/BusinessIdentifier'
       ) item
where ses.seq_id = p_seq_id;

select item.*
from som_edi_customer_source ses,
     xmltable(
        --xmlnamespaces('http://schemas.microsoft.com/xxx.dtd' as "ns"),
        xmlnamespaces(default 'http://schemas.microsoft.com/xxx.dtd'),
        '/Pip3A4PurchaseOrderRequest/PurchaseOrder/ProductLineItem'
        passing xmltype(ses.memo)
        columns
          cust_name varchar2(1000) path'CustomerInformation/businessName',
          cust_po_number varchar2(1000) path 'PartnerBusiness/BusinessIdentifier'
      ) item
where ses.seq_id = p_seq_id;

function remove_namespace(p_xml_clob clob) return clob is
  l_return clob;
  l_taiwan_namespace varchar2(100) := 'xmlns="http://schemas.microsoft.com/xxx.dtd"';
  l_china_namespace  varchar2(100) := 'xmlns="http://schemas.microsoft.com/yyy.dtd"';
begin
  begin
    select replace(replace(p_xml_clob, l_china_namespace, null),
                   l_taiwan_namespace,
                   null)
      into l_return
      from dual;
  end;
  return l_return;
end remove_namespace;

 select item.*
  from (select seq_id, remove_namespace(memo) as memo 
          from som_edi_customer_source) ses,
       xmltable(
         '/Pip3A4PurchaseOrderRequest/PurchaseOrder/ProductLineItem'
         passing xmltype(ses.memo)
         columns
           cust_name varchar2(1000) path'CustomerInformation/businessName',
           cust_po_number varchar2(1000) path 'PartnerBusiness/BusinessIdentifier'
       ) item
where ses.seq_id = p_seq_id;

select (
    xmlelement("Pip3A4PurchaseOrderConfirmation",
      --add default namespace
      xmlattributes('microsoft.com' as "xmlns:prx"),
      xmlelement("PurchaseOrder",
        ( -- line loop
          select (
            xmlelement("ProductLineItem",
              xmlelement("buyerLineItem",
                xmlelement("LineNumber", sel.line_number)
              )
              xmlelement("OrderQuantity",
                xmlelement("requestedQuantity",
                  xmlelement("ProductQuantity", order_line.ordered_quantity)
                )
              )
            )
          )
           from oe_order_lines_all order_line,
                som_edi_customer_3a4_line sel
          where order_line.header_id = order_header.header_id
            and order_line.customer_line_number = sel.po_line_number
            and sel.header_id = seh.header_id
        ) as "line"
      ),
      xmlelement("requestingDocumentDateTime",
        xmlelement("DateTimeStamp", som_edi_customer_pkg.get_customer_format_date(sysdate))
      )
    )
  ).getClobVal() as "header" into l_clob_xml
from oe_order_headers_all         order_header,
     som_edi_customer_3a4_header  seh
where 1 = 1
  and order_header.cust_po_number = seh.po_number
  and seh.po_number = p_customer_po;

CREATE TABLE xml_table (
    id NUMBER,
    xml_data SYS.XMLTYPE
);