2012년 10월 10일 수요일

[JSP] xml 포맷 그대로 표현하기

기존 JSP에서 xml데이터 포맷 그대로 보여주고 싶을 때
(실제로 html과 xml은 언어 자체가 달라서 두 개를 같이 표현을 해 줄 수가 없다. 찾아보니 그러한 기능을 js와 css를 사용해서 구현한 사이트가 있어서 샘플을 만들었다. 
구글링 만세!)

-------------------------------------------------------
index.jsp
-------------------------------------------------------
<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<title>Insert title here</title>
</head>
<body>
<div id="XMLHolder"></div>
<LINK href='XMLDisplay.css' type='text/css' rel='stylesheet'>
<script type='text/javascript' src='XMLDisplay.js'></script>
<script>LoadXML('XMLHolder','test.xml');</script>
</body>
</html>

**테스트용이었으므로 css파일, js파일, jsp파일 모두 같은 위치에 배치

-------------------------------------------------------
XMLDisplay.js
-------------------------------------------------------
/* Copyright (c) 2007 Lev Muchnik <LevMuchnik@gmail.com>. All rights reserved.
 * You may copy and modify this script as long as the above copyright notice,
 * this condition and the following disclaimer is left intact.
 * This software is provided by the author "AS IS" and no warranties are
 * implied, including fitness for a particular purpose. In no event shall
 * the author be liable for any damages arising in any way out of the use
 * of this software, even if advised of the possibility of such damage.
 * $Date: 2007-10-03 19:08:15 -0700 (Wed, 03 Oct 2007) $
 */

function LoadXML(ParentElementID,URL) 
{
var xmlHolderElement = GetParentElement(ParentElementID);
if (xmlHolderElement==null) { return false; }
ToggleElementVisibility(xmlHolderElement);
return RequestURL(URL,URLReceiveCallback,ParentElementID);
}
function LoadXMLDom(ParentElementID,xmlDoc) 
{
if (xmlDoc) {
var xmlHolderElement = GetParentElement(ParentElementID);
if (xmlHolderElement==null) { return false; }
while (xmlHolderElement.childNodes.length) { xmlHolderElement.removeChild(xmlHolderElement.childNodes.item(xmlHolderElement.childNodes.length-1)); }
var Result = ShowXML(xmlHolderElement,xmlDoc.documentElement,0);
var ReferenceElement = document.createElement('div');
var Link = document.createElement('a');
Link.setAttribute('href','http://www.levmuchnik.net/Content/ProgrammingTips/WEB/XMLDisplay/DisplayXMLFileWithJavascript.html');
//var TextNode = document.createTextNode('Source: Lev Muchnik');
Link.appendChild(TextNode);

xmlHolderElement.appendChild(Link);
return Result;
}
else { return false; }
}
function LoadXMLString(ParentElementID,XMLString) 
{
xmlDoc = CreateXMLDOM(XMLString);
return LoadXMLDom(ParentElementID,xmlDoc) ;
}
////////////////////////////////////////////////////////////
// HELPER FUNCTIONS - SHOULD NOT BE DIRECTLY CALLED BY USERS
////////////////////////////////////////////////////////////
function GetParentElement(ParentElementID)
{
if (typeof(ParentElementID)=='string') { return document.getElementById(ParentElementID); }
else if (typeof(ParentElementID)=='object') { return ParentElementID;} 
else { return null; }
}
function URLReceiveCallback(httpRequest,xmlHolderElement)
{
 try {
            if (httpRequest.readyState == 4) {
                if (httpRequest.status == 200) {
var xmlDoc = httpRequest.responseXML;
if (xmlHolderElement && xmlHolderElement!=null) {
xmlHolderElement.innerHTML = '';
return LoadXMLDom(xmlHolderElement,xmlDoc);
}
                } else {
                    return false;
                }
            }
        }
        catch( e ) {
            return false;
        }
}
function RequestURL(url,callback,ExtraData) { // based on: http://developer.mozilla.org/en/docs/AJAX:Getting_Started
        var httpRequest;
        if (window.XMLHttpRequest) { // Mozilla, Safari, ...
            httpRequest = new XMLHttpRequest();
            if (httpRequest.overrideMimeType) { httpRequest.overrideMimeType('text/xml'); }
        } 
        else if (window.ActiveXObject) { // IE
            try { httpRequest = new ActiveXObject("Msxml2.XMLHTTP");   } 
            catch (e) {
  try { httpRequest = new ActiveXObject("Microsoft.XMLHTTP"); } 
  catch (e) {}
            }
        }
        if (!httpRequest) { return false;   }
        httpRequest.onreadystatechange = function() { callback(httpRequest,ExtraData); };
        /* xml위치 : /WebContent/file/ */
        httpRequest.open('GET', "http://localhost:8080/Text/file/"+url, true);
        httpRequest.send('');
return true;
    }
function CreateXMLDOM(XMLStr) 
{
if (window.ActiveXObject)
{
 xmlDoc=new ActiveXObject("Microsoft.XMLDOM"); 
 xmlDoc.loadXML(XMLStr);
 return xmlDoc;
}
else if (document.implementation && document.implementation.createDocument)  {
 var parser=new DOMParser();
 return parser.parseFromString(XMLStr,"text/xml");
}
else {
return null;
}
}

var IDCounter = 1;
var NestingIndent = 40;
function ShowXML(xmlHolderElement,RootNode,indent)
{
if (RootNode==null || xmlHolderElement==null) { return false; }
var Result  = true;
var TagEmptyElement = document.createElement('div');
TagEmptyElement.className = 'Element';
TagEmptyElement.style.position = 'relative';
TagEmptyElement.style.left = NestingIndent+'px';
if (RootNode.childNodes.length==0) { 
    var ClickableElement = AddTextNode(TagEmptyElement,'','Clickable') ;
    ClickableElement.id = 'div_empty_' + IDCounter;  
    AddTextNode(TagEmptyElement,'<','Utility') ;
    AddTextNode(TagEmptyElement,RootNode.nodeName ,'NodeName') 
    for (var i = 0; RootNode.attributes && i < RootNode.attributes.length; ++i) {
      CurrentAttribute  = RootNode.attributes.item(i);
      AddTextNode(TagEmptyElement,' ' + CurrentAttribute.nodeName ,'AttributeName') ;
      AddTextNode(TagEmptyElement,'=','Utility') ;
      AddTextNode(TagEmptyElement,'"' + CurrentAttribute.nodeValue + '"','AttributeValue') ;
    }
    AddTextNode(TagEmptyElement,' />') ;
    xmlHolderElement.appendChild(TagEmptyElement);
    //SetVisibility(TagEmptyElement,true);    
}
else { // mo child nodes
    
    var ClickableElement = AddTextNode(TagEmptyElement,'+','Clickable') ;
    ClickableElement.onclick  = function() {ToggleElementVisibility(this); }
    ClickableElement.id = 'div_empty_' + IDCounter;
    AddTextNode(TagEmptyElement,'<','Utility') ;
    AddTextNode(TagEmptyElement,RootNode.nodeName ,'NodeName') 
    for (var i = 0; RootNode.attributes && i < RootNode.attributes.length; ++i) {
      CurrentAttribute  = RootNode.attributes.item(i);
      AddTextNode(TagEmptyElement,' ' + CurrentAttribute.nodeName ,'AttributeName') ;
      AddTextNode(TagEmptyElement,'=','Utility') ;
      AddTextNode(TagEmptyElement,'"' + CurrentAttribute.nodeValue + '"','AttributeValue') ;
    }

    AddTextNode(TagEmptyElement,'>  </','Utility') ;
    AddTextNode(TagEmptyElement,RootNode.nodeName,'NodeName') ;
    AddTextNode(TagEmptyElement,'>','Utility') ;
    xmlHolderElement.appendChild(TagEmptyElement);
    SetVisibility(TagEmptyElement,false);
    //----------------------------------------------
    
    var TagElement = document.createElement('div');
    TagElement.className = 'Element';
    TagElement.style.position = 'relative';
    TagElement.style.left = NestingIndent+'px';
    ClickableElement = AddTextNode(TagElement,'-','Clickable') ;
    ClickableElement.onclick  = function() {ToggleElementVisibility(this); }
    ClickableElement.id = 'div_content_' + IDCounter;
    ++IDCounter;
    AddTextNode(TagElement,'<','Utility') ;
    AddTextNode(TagElement,RootNode.nodeName ,'NodeName') ;
    
    for (var i = 0; RootNode.attributes && i < RootNode.attributes.length; ++i) {
        CurrentAttribute  = RootNode.attributes.item(i);
        AddTextNode(TagElement,' ' + CurrentAttribute.nodeName ,'AttributeName') ;
        AddTextNode(TagElement,'=','Utility') ;
        AddTextNode(TagElement,'"' + CurrentAttribute.nodeValue + '"','AttributeValue') ;
    }
    AddTextNode(TagElement,'>','Utility') ;
    TagElement.appendChild(document.createElement('br'));
    var NodeContent = null;
    for (var i = 0; RootNode.childNodes && i < RootNode.childNodes.length; ++i) {
      if (RootNode.childNodes.item(i).nodeName != '#text') {
        Result &= ShowXML(TagElement,RootNode.childNodes.item(i),indent+1);
      }
      else {
        NodeContent =RootNode.childNodes.item(i).nodeValue;
      }
    }
    if (RootNode.nodeValue) {
      NodeContent = RootNode.nodeValue;
    }
    if (NodeContent) {
      var ContentElement = document.createElement('div');
      ContentElement.style.position = 'relative';
      ContentElement.style.left = NestingIndent+'px';
      AddTextNode(ContentElement,NodeContent ,'NodeValue') ;
      TagElement.appendChild(ContentElement);
    }
    AddTextNode(TagElement,'  </','Utility') ;
    AddTextNode(TagElement,RootNode.nodeName,'NodeName') ;
    AddTextNode(TagElement,'>','Utility') ;
    xmlHolderElement.appendChild(TagElement);
  }
// if (indent==0) { ToggleElementVisibility(TagElement.childNodes(0)); } - uncomment to collapse the external element
return Result;
}
function AddTextNode(ParentNode,Text,Class) 
{
NewNode = document.createElement('span');
if (Class) {  NewNode.className  = Class;}
if (Text) { NewNode.appendChild(document.createTextNode(Text)); }
if (ParentNode) { ParentNode.appendChild(NewNode); }
return NewNode;
}
function CompatibleGetElementByID(id)
{
if (!id) { return null; }
if (document.getElementById) { // DOM3 = IE5, NS6
return document.getElementById(id);
}
else {
if (document.layers) { // Netscape 4
return document.id;
}
else { // IE 4
return document.all.id;
}
}
}
function SetVisibility(HTMLElement,Visible)
{
if (!HTMLElement) { return; }
var VisibilityStr  = (Visible) ? 'block' : 'none';
if (document.getElementById) { // DOM3 = IE5, NS6
HTMLElement.style.display =VisibilityStr; 
}
else {
if (document.layers) { // Netscape 4
HTMLElement.display = VisibilityStr; 
}
else { // IE 4
HTMLElement.id.style.display = VisibilityStr; 
}
}
}
function ToggleElementVisibility(Element)
{
if (!Element|| !Element.id) { return; }
try {
ElementType = Element.id.slice(0,Element.id.lastIndexOf('_')+1);
ElementID = parseInt(Element.id.slice(Element.id.lastIndexOf('_')+1));
}
catch(e) { return ; }
var ElementToHide = null;
var ElementToShow= null;
if (ElementType=='div_content_') {
ElementToHide = 'div_content_' + ElementID;
ElementToShow = 'div_empty_' + ElementID;
}
else if (ElementType=='div_empty_') {
ElementToShow= 'div_content_' + ElementID;
ElementToHide  = 'div_empty_' + ElementID;
}
ElementToHide = CompatibleGetElementByID(ElementToHide);
ElementToShow = CompatibleGetElementByID(ElementToShow);
if (ElementToHide) { ElementToHide = ElementToHide.parentNode;}
if (ElementToShow) { ElementToShow = ElementToShow.parentNode;}
SetVisibility(ElementToHide,false);
SetVisibility(ElementToShow,true);
}

-------------------------------------------------------
XMLDisplay.css
-------------------------------------------------------
@charset "utf-8";
/* CSS Document */
.Utility {
color: black;
}
.NodeName {
font-weight:bold;
color: #800080;
}
.AttributeName 
{
font-weight:bold;
color: black;
}
.AttributeValue
{
color:blue;
}
.NodeValue
{
color: black;
}
.Element {
border-left-color:#FFFFFF;
border-left-width:thin;
border-left-style:solid;
padding-top:0px;
margin-top:10px;
}
.Clickable {
font-weight:900;
font-size:large;
color: #800080;
cursor:pointer;

vertical-align:middle;
}



[oracle] sys_connect_by_path와 대체 쿼리

SYS_CONNECT_BY_PATH
계층형 쿼리에서 하위 레벨의 특정 컬럼들을 한 줄로 표현할 때 사용
ex)
LEVEL COLNM
1         A
2         A1
2         A2
2         A3
1         B
2         B1
2         B2
2         B3

COLNM    SYS_CONNECT_BY_PATH
A             A1,A2,A3
B             B1,B2,B3

모든 사람들이 접근 가능하고 테이블 create하지 않고 조회할 수 있는 scott/tiger로 작업
---------------------------------------------------------------------
SYS_CONNECT_BY_PATH
---------------------------------------------------------------------
SELECT
DEPTNO
,SUBSTR (MAX (SYS_CONNECT_BY_PATH (ename, ',')), 2) ENAME
FROM (
SELECT
DEPTNO
,ENAME
,ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY ENAME) AS RNO
FROM EMP)
START WITH RNO = 1
CONNECT BY PRIOR RNO = RNO-1
AND PRIOR DEPTNO = DEPTNO
GROUP BY DEPTNO
ORDER BY DEPTNO

* 퍼포먼스면에서 문제가 많다고들 한다.

꼭 계층형 쿼리를 써야 하는 부분이 아니라면, 다른 함수들을 사용하는 것이 좋겠지.

---------------------------------------------------------------------
XMLELEMENT
---------------------------------------------------------------------
SELECT
DEPTNO
,SUBSTR(XMLAGG(XMLELEMENT(A,','|| ENAME) ORDER BY ENAME).EXTRACT('//text()'),2) ENAME
FROM EMP
GROUP BY DEPTNO;


---------------------------------------------------------------------
WM_CONCAT
---------------------------------------------------------------------
SELECT
A.DEPTNO
,MAX(A.ENAME) ENAME
FROM (
SELECT
DEPTNO
,WM_CONCAT(ENAME) OVER(PARTITION BY DEPTNO ORDER BY ENAME) ENAME
FROM EMP) A
GROUP BY DEPTNO;