この記事は約41分44秒で読むことができます。

xml関数にふれる

参考文献

HTML特殊文字変換ツール

xmlserialize

indentのsize指定とdocument指定&clob型返却知っておきゃいいんじゃないかな

XMLSERIALIZE  
 
8.2.7 XMLSERIALIZE SQL/XML Function  
 
コード表示

SELECT
	XMLSERIALIZE(DOCUMENT xmltype('<PivotSet> <item> <column name="STF">Butter</column> <column name="NM">Cookies</column> </item> <item> <column name="STF">Chocolate</column> <column name="NM">Cookies</column> </item> <item> <column name="STF">Eggs</column> <column name="NM">Cookies</column> </item> <item> <column name="STF">Flour</column> <column name="NM">Cookies</column> </item> <item> <column name="STF">Sugar</column> <column name="NM">Cookies</column> </item> </PivotSet>'
) AS CLOB INDENT SIZE = 4) AS xmldoc
FROM
	dual;

インデント4つになった!

コード表示

"<PivotSet>
    <item>
        <column name="STF">Butter</column>
        <column name="NM">Cookies</column>
    </item>
    <item>
        <column name="STF">Chocolate</column>
        <column name="NM">Cookies</column>
    </item>
    <item>
        <column name="STF">Eggs</column>
        <column name="NM">Cookies</column>
    </item>
    <item>
        <column name="STF">Flour</column>
        <column name="NM">Cookies</column>
    </item>
    <item>
        <column name="STF">Sugar</column>
        <column name="NM">Cookies</column>
    </item>
</PivotSet>
"

xmlelement

xmltype型つくる

8.2.1.3 XMLElement Examples
コード表示

SELECT * FROM test___rcp___;
コード表示

AINE@pdb1> SELECT * FROM test___rcp___;

       RCP_ID NM
------------- --------------------
          100 Cookies
          200 Brownies
          300 Pancakes

3 rows selected.

Elapsed: 00:00:00.01

テーブル名変えて使う

コード表示

WITH create_snippet AS (
	SELECT
		s1.table_name
		, DENSE_RANK() OVER(
			ORDER BY
				s1.table_name
		) AS rn
		, s1.column_id
		, regexp_replace(
			CASE
				WHEN NOT EXISTS(
					SELECT
						1
					FROM
						user_tab_cols s2
					WHERE
						s1.table_name = s2.table_name
						AND s1.column_id > s2.column_id
				) THEN 'with sub as ( select '
				       || (
					SELECT
						LISTAGG(s2.column_name, ',') WITHIN GROUP(
							ORDER BY
								s2.column_id
						)
					FROM
						user_tab_cols s2
					WHERE
						s1.table_name = s2.table_name
				)
				       || ',xmlelement('
				       || '"'
				       || s1.table_name
				       || '"'
				ELSE ''
			END
			|| ',xmlelement('
			|| '"'
			|| s1.column_name
			|| '",'
			|| s1.column_name
			|| ')'
			||
			CASE
				WHEN NOT EXISTS(
					SELECT
						1
					FROM
						user_tab_cols s2
					WHERE
						s1.table_name = s2.table_name
						AND s1.column_id < s2.column_id
				) THEN ' ) as xml_ele from '
				       || s1.table_name
				       || ')select '
				       || (
					SELECT
						LISTAGG(s2.column_name, ',') WITHIN GROUP(
							ORDER BY
								s2.column_id
						)
					FROM
						user_tab_cols s2
					WHERE
						s1.table_name = s2.table_name
				)
				       || ',FMT_XML(xml_ele) as xml_doc from sub;'
				ELSE ''
			END
		, '\s{1,}', ' ') AS snippet_sql
	FROM
		user_tab_cols s1
) SELECT
	s1.rn
	, s1.table_name
	, LISTAGG(s1.snippet_sql) WITHIN GROUP(
		ORDER BY
			s1.column_id
	) AS build_sql
  FROM
	create_snippet s1
  WHERE
	s1.table_name = 'TEST___RCP___'
  GROUP BY
	s1.table_name
	, s1.rn;
コード表示

WITH sub AS (
	SELECT
		rcp_id
		, nm
		, XMLELEMENT(
			"TEST___RCP___"
			, XMLELEMENT(
				"RCP_ID"
				, rcp_id
			), XMLELEMENT(
				"NM"
				, nm
			)
		) AS xml_ele
	FROM
		test___rcp___
) SELECT
	rcp_id
	, nm
	, fmt_xml(xml_ele) AS xml_doc
  FROM
	sub;
コード表示

AINE@pdb1> col xml_doc for a100
AINE@pdb1> with sub as ( select RCP_ID,NM,xmlelement("TEST___RCP___",xmlelement("RCP_ID",RCP_ID),xmlelement("NM",NM) ) as xml_ele from TEST___RCP___)select RCP_ID,NM,FMT_XML(xml_ele) as xml_doc from sub;

       RCP_ID NM                   XML_DOC
------------- -------------------- ----------------------------------------------------------------------------------------------------
          100 Cookies              <TEST___RCP___>
                                     <RCP_ID>100</RCP_ID>
                                     <NM>Cookies</NM>
                                   </TEST___RCP___>

          200 Brownies             <TEST___RCP___>
                                     <RCP_ID>200</RCP_ID>
                                     <NM>Brownies</NM>
                                   </TEST___RCP___>

          300 Pancakes             <TEST___RCP___>
                                     <RCP_ID>300</RCP_ID>
                                     <NM>Pancakes</NM>
                                   </TEST___RCP___>


3 rows selected.

Elapsed: 00:00:00.01

xmlattributes

Example 8-6 Creating a Schema-Based XML Document Using XMLELEMENT with Namespaces

xmlattributesで属性指定

コード表示

WITH sub AS (
	SELECT
		rcp_id
		, nm
		, XMLELEMENT(
			"TEST___RCP___"
			, XMLELEMENT(
				"RCP_ID"
				, XMLATTRIBUTES(
					rcp_id AS "RCP_ID"
				)
				, rcp_id
			), XMLELEMENT(
				"NM"
				, XMLATTRIBUTES(
					nm AS "NM"
				)
				, nm
			)
		) AS xml_ele
	FROM
		test___rcp___
) SELECT
	rcp_id
	, nm
	, fmt_xml(xml_ele) AS xml_doc
  FROM
	sub;

たいへんだなこれは

コード表示

WITH sub AS (
	SELECT
		rcp_id
		, nm
		, XMLELEMENT(
			"TEST___RCP___"
			, XMLELEMENT(
				"RCP_ID"
				, XMLATTRIBUTES(
					rcp_id AS "RCP_ID"
				)
				, rcp_id
			), XMLELEMENT(
				"NM"
				, XMLATTRIBUTES(
					nm AS "NM"
				)
				, nm
			)
		) AS xml_ele
	FROM
		test___rcp___
) SELECT
	rcp_id
	, nm
	, fmt_xml(xml_ele) AS xml_doc
  FROM
	sub;

属性でたー

コード表示

AINE@pdb1> WITH sub AS ( SELECT rcp_id , nm , XMLELEMENT( "TEST___RCP___" , XMLELEMENT( "RCP_ID" , XMLATTRIBUTES( rcp_id AS "RCP_ID" ) , rcp_id ), XMLELEMENT( "NM" , XMLATTRIBUTES( nm AS "NM" ) , nm ) ) AS xml_ele FROM test___rcp___ ) SELECT rcp_id , nm , fmt_xml(xml_ele) AS xml_doc FROM sub;

       RCP_ID NM                   XML_DOC
------------- -------------------- ----------------------------------------------------------------------------------------------------
          100 Cookies              <TEST___RCP___>
                                     <RCP_ID RCP_ID="100">100</RCP_ID>
                                     <NM NM="Cookies">Cookies</NM>
                                   </TEST___RCP___>

          200 Brownies             <TEST___RCP___>
                                     <RCP_ID RCP_ID="200">200</RCP_ID>
                                     <NM NM="Brownies">Brownies</NM>
                                   </TEST___RCP___>

          300 Pancakes             <TEST___RCP___>
                                     <RCP_ID RCP_ID="300">300</RCP_ID>
                                     <NM NM="Pancakes">Pancakes</NM>
                                   </TEST___RCP___>


3 rows selected.

Elapsed: 00:00:00.00

xmlforest

キュッとまとめるやつカンマで。カラム名とかはこいつ使える!forest2個でてくるけど、メンテつかれたー。コメントアウトで対応。。。

コード表示

WITH create_snippet AS (
	SELECT
		s1.table_name
		, DENSE_RANK() OVER(
			ORDER BY
				s1.table_name
		) AS rn
		, s1.column_id
		, regexp_replace(
			CASE
				WHEN NOT EXISTS(
					SELECT
						1
					FROM
						user_tab_cols s2
					WHERE
						s1.table_name = s2.table_name
						AND s1.column_id > s2.column_id
				) THEN 'with sub as ( select '
				       || (
					SELECT
						LISTAGG(s2.column_name, ',') WITHIN GROUP(
							ORDER BY
								s2.column_id
						)
					FROM
						user_tab_cols s2
					WHERE
						s1.table_name = s2.table_name
				)
				       || ',xmlelement('
				       || '"'
				       || s1.table_name
				       || '"'
				ELSE ''
			END
			|| ',xmlforest('
				       || (
					SELECT
						LISTAGG(s2.column_name, ',') WITHIN GROUP(
							ORDER BY
								s2.column_id
						)
					FROM
						user_tab_cols s2
					WHERE
						s1.table_name = s2.table_name
				)
			|| ')'
			||
			CASE
				WHEN NOT EXISTS(
					SELECT
						1
					FROM
						user_tab_cols s2
					WHERE
						s1.table_name = s2.table_name
						AND s1.column_id < s2.column_id
				) THEN ' ) as xml_ele from '
				       || s1.table_name
				       || ')select '
				       || (
					SELECT
						LISTAGG(s2.column_name, ',') WITHIN GROUP(
							ORDER BY
								s2.column_id
						)
					FROM
						user_tab_cols s2
					WHERE
						s1.table_name = s2.table_name
				)
				       || ',FMT_XML(xml_ele) as xml_doc from sub;'
				ELSE ''
			END
		, '\s{1,}', ' ') AS snippet_sql
	FROM
		user_tab_cols s1
) SELECT
	s1.rn
	, s1.table_name
	, LISTAGG(s1.snippet_sql) WITHIN GROUP(
		ORDER BY
			s1.column_id
	) AS build_sql
  FROM
	create_snippet s1
  WHERE
	s1.table_name = 'TEST___RCP___'
  GROUP BY
	s1.table_name
	, s1.rn;

コメントアウト!

コード表示

WITH sub AS (
	SELECT
		rcp_id
		, nm
		, XMLELEMENT(
			"TEST___RCP___"
			, XMLFOREST(rcp_id, nm)
--			, XMLFOREST(rcp_id, nm)
		) AS xml_ele
	FROM
		test___rcp___
) SELECT
	rcp_id
	, nm
	, fmt_xml(xml_ele) AS xml_doc
  FROM
	sub;

XMLELEMENTと同じ結果が得られた

コード表示

AINE@pdb1> WITH sub AS ( SELECT rcp_id , nm , XMLELEMENT( "TEST___RCP___" , XMLFOREST(rcp_id, nm) ) AS xml_ele FROM test___rcp___ ) SELECT rcp_id , nm , fmt_xml(xml_ele) AS xml_doc FROM sub;

       RCP_ID NM                   XML_DOC
------------- -------------------- ----------------------------------------------------------------------------------------------------
          100 Cookies              <TEST___RCP___>
                                     <RCP_ID>100</RCP_ID>
                                     <NM>Cookies</NM>
                                   </TEST___RCP___>

          200 Brownies             <TEST___RCP___>
                                     <RCP_ID>200</RCP_ID>
                                     <NM>Brownies</NM>
                                   </TEST___RCP___>

          300 Pancakes             <TEST___RCP___>
                                     <RCP_ID>300</RCP_ID>
                                     <NM>Pancakes</NM>
                                   </TEST___RCP___>


3 rows selected.

Elapsed: 00:00:00.00

xmlaggは気合入れたい!


12cR2の新機能LISTAGGオーバーフローコントロールを他のバージョンでも使える単一SQLクエリで書いてみる
 
 

【oracle】LISTAGGの代わりにXMLAGGを使う
 
 
コード表示

WITH create_snippet AS (
	SELECT
		s1.table_name
		, DENSE_RANK() OVER(
			ORDER BY
				s1.table_name
		) AS rn
		, s1.column_id
		, regexp_replace(
			CASE
				WHEN NOT EXISTS(
					SELECT
						1
					FROM
						user_tab_cols s2
					WHERE
						s1.table_name = s2.table_name
						AND s1.column_id > s2.column_id
				) THEN 'with sub as ( select '
				       || 'xmlelement('
				       || '"'
				       || s1.table_name
				       || '"'
				       || ',xmlagg('
				       || (
					SELECT
						LISTAGG('xmlelement('
						          || '"'
						          || s2.column_name
						          || '",'
						          || s2.column_name, ',') WITHIN GROUP(
							ORDER BY
								s2.column_id
						)
						|| ')'
					FROM
						user_tab_cols s2
					WHERE
						s1.table_name = s2.table_name
				)
				       || ')'
				       || ')'
				ELSE ''
			END
			||
			CASE
				WHEN NOT EXISTS(
					SELECT
						1
					FROM
						user_tab_cols s2
					WHERE
						s1.table_name = s2.table_name
						AND s1.column_id < s2.column_id
				) THEN ' ) as xml_ele from '
				       || s1.table_name
				       || ')select '
				       || 'FMT_XML(xml_ele) as xml_doc from sub;'
				ELSE ''
			END
		, '\s{1,}', ' ') AS snippet_sql
	FROM
		user_tab_cols s1
) SELECT
	s1.rn
	, s1.table_name
	, LISTAGG(s1.snippet_sql) WITHIN GROUP(
		ORDER BY
			s1.column_id
	) AS build_sql
  FROM
	create_snippet s1
  WHERE
	s1.table_name = 'TEST___STF___'
  GROUP BY
	s1.table_name
	, s1.rn;

できた!

コード表示

WITH sub AS (
	SELECT
		XMLELEMENT(
			"TEST___STF___"
			, XMLAGG(XMLELEMENT(
				"RCP_ID"
				, rcp_id, XMLELEMENT(
					"STF"
					, stf
				)
			) )
		) AS xml_ele
	FROM
		test___stf___
) SELECT
	fmt_xml(xml_ele) AS xml_doc
  FROM
	sub;

おぉぉ

コード表示

"<TEST___STF___>
  <RCP_ID>100<STF>Butter</STF>
  </RCP_ID>
  <RCP_ID>100<STF>Chocolate</STF>
  </RCP_ID>
  <RCP_ID>100<STF>Eggs</STF>
  </RCP_ID>
  <RCP_ID>100<STF>Flour</STF>
  </RCP_ID>
  <RCP_ID>100<STF>Sugar</STF>
  </RCP_ID>
  <RCP_ID>200<STF>Butter</STF>
  </RCP_ID>
  <RCP_ID>200<STF>Cocoa</STF>
  </RCP_ID>
  <RCP_ID>200<STF>Flour</STF>
  </RCP_ID>
  <RCP_ID>200<STF>Sugar</STF>
  </RCP_ID>
  <RCP_ID>300<STF>Eggs</STF>
  </RCP_ID>
  <RCP_ID>300<STF>Flour</STF>
  </RCP_ID>
  <RCP_ID>300<STF>Milk</STF>
  </RCP_ID>
</TEST___STF___>
"

型はこんな感じだきっと

コード表示

xmlelement(
			"tagname"
			,val
			,xmlattributes
			,xmlagg(
				xmlelement(
					"tagname"
					,val
					,xmlattributes(
						val as "attrname"
					)
					,xmlagg(
						xmlelement(
							"tagname"
							,val
							,xmlattributes(
								val as "attrname"
							)
						)
					)
				)
			)
		)

xmltable

ダイナミックSQLを生成し実行結果を得る単一SQL ステートメントの作り方(Oracle)

おまけ


図形構文図

Leave a Reply

Your email address will not be published. Required fields are marked *