XMLQUERY演算�

XMLQUERYã?¯SQLã?«ã?¦XMLã?®å€¤ã‚’å•?ã?„å?ˆã‚?ã?›ã‚‹ã?“ã?¨ã?Œã?§ã??ã‚‹SQL/XML演算å­?ã?§ã?™ã€‚

XMLQUERY演算���二��引数��り��。片方�XML������も�一��DerbyXML値��。

æ§‹æ–‡

XMLQUERY ( xquery文字列リテラル
    PASSING BY REF xml値� 
    [ RETURNING SEQUENCE [ BY REF ] ]
    EMPTY ON EMPTY
    )
xquery文字列リテラル
文字列ã?®ãƒªãƒ†ãƒ©ãƒ«ã?ŒæŒ‡å®šã?•れã?ªã?‘れã?°ã?ªã‚Šã?¾ã?›ã‚“。もã?—引数ã?«ãƒ‘ラメータã?Œä¸Žã?ˆã‚‰ã‚Œã?Ÿã‚Šã€?リテラルã?§ã?¯ã?ªã?„å¼?ã?Œä¸Žã?ˆã‚‰ã‚Œã?Ÿã‚Šã€?文字列ã?§ã?¯ã?ªã?„(æ•´æ•°ã?®ã‚ˆã?†ã?ª)リテラルã?Œä¸Žã?ˆã‚‰ã‚Œã?Ÿå ´å?ˆã€?Derbyã?¯ã‚¨ãƒ©ãƒ¼ã‚’挙ã?’ã?¾ã?™ã€‚ 引数ã?®xquery文字列リテラルã?¯ã€?Apache Xalanã?Œã‚µãƒ?ートã?™ã‚‹XPathå¼?ã?§ã?ªã?‘れã?°ã?ªã‚Šã?¾ã?›ã‚“。Derbyã?¯ã?©ã?®ã‚ˆã?†ã?ªXMLå•?å?ˆã?›å¼?を評価ã?™ã‚‹ã?§ã‚‚Apache Xalanを利用ã?—ã?¾ã?™ã€‚Xalanã?¯XQueryを完全ã?«ã‚µãƒ?ートã?—ã?ªã?„ã?®ã?§ã€?Derbyã‚‚å?Œæ§˜ã?«å®Œå…¨ã?«ã‚µãƒ?ートã?—ã?¾ã?›ã‚“。 ã‚‚ã?—Xalanã?Œå•?å?ˆã?›ã?®ã‚³ãƒ³ãƒ‘イルや実行を行ã?ˆã?ªã?‹ã?£ã?Ÿå ´å?ˆã€?Derbyã?¯Xalanã?ŒæŒ™ã?’ã?Ÿã‚¨ãƒ©ãƒ¼ã‚’補足ã?—ã?¦ã€?SQLExceptionã?¨ã?—ã?¦æŒ™ã?’ã?ªã?Šã?—ã?¾ã?™ã€‚ XPathã?Šã‚ˆã?³XQueryå¼?ã?®è©³ç´°ã?«ã?¤ã?„ã?¦ã?¯ã€?次ã?®Webサイトをå?‚ç…§ã?—ã?¦ã??ã? ã?•ã?„。http://www.w3.org/TR/xpathã?¨http://www.w3.org/TR/xquery/
xml値�
ã?“れã?¯XMLã?®å€¤ã?§ã?ªã?‘れã?°ã?ªã‚‰ã?šã€?ã?¾ã?Ÿæ•´å½¢å¼?ã?®SQL/XML文書ã?§ã?ªã?‘れã?°ã?ªã‚Šã?¾ã?›ã‚“。 xml値å¼?ã?¯ãƒ‘ラメータã?§ã?‚ã?£ã?¦ã?¯ã?ªã‚Šã?¾ã?›ã‚“。 Derbyã?¯æš—é»™ã?®æ§‹æ–‡è§£æž?ã‚„ã€?XML値ã?¸ã?®å¤‰æ?›ã‚’行ã‚?ã?ªã?„ã?®ã?§ã€?文字列やã??ã?®ä»–ã?®ãƒ‡ãƒ¼ã‚¿åž‹ã‚’使ã?†ã?¨ã‚¨ãƒ©ãƒ¼ã?¨ã?ªã‚Šã?¾ã?™ã€‚ ã‚‚ã?—引数ã?ŒDerbyã?®XMLQUERY演算å­?ã?«ã‚ˆã‚Šè¿”る並ã?³ã?§ã?‚ã?£ã?Ÿå ´å?ˆã€?ã‚‚ã?—ã??ã?®ä¸¦ã?³ã?Œä¸€ã?¤ã?®æ–‡æ›¸ãƒŽãƒ¼ãƒ‰ã?§ã?‚るノードã?‹ã‚‰ã?ªã‚‹ä¸¦ã?³ã?§ã?‚れã?°ã€?引数ã?«ã?¨ã‚‹äº‹ã?Œã?§ã??ã?¾ã?™ã€‚ã??ã?†ã?§ã?¯ã?ªã?‘れã?°ã€?Derbyã?¯ã‚¨ãƒ©ãƒ¼ã‚’挙ã?’ã?¾ã?™ã€‚
BY REF
ã?“ã?®çœ?ç•¥å?¯èƒ½ã?ªã‚­ãƒ¼ãƒ¯ãƒ¼ãƒ‰ã?«ã‚ˆã‚Šã€?Derbyã?«ã?¦å?¯èƒ½ã?ªå¼•数渡ã?—ã?®ä»•組ã?¿ã?Œè¨˜è¿°ã?•れã?¾ã?™ã€‚BY REFã?¯è¦?定ã?®å¼•数渡ã?—ã?®ä»•組ã?¿ã?ªã?®ã?§ã€?ã?“ã?®ã‚­ãƒ¼ãƒ¯ãƒ¼ãƒ‰ã?®ã?‚ã‚‹ã?ªã?—ã?«ã?‹ã?‹ã‚?らã?šã€?XMLQUERY演算å­?ã?¯å?Œæ§˜ã?«æŒ¯èˆžã?„ã?¾ã?™ã€‚引数渡ã?—ã?®ä»•組ã?¿ã?«ã?¤ã?„ã?¦ã?®ã‚ˆã‚Šè©³ç´°ã?ªæƒ…å ±ã?¯ã€?SQL/XML仕様をå?‚ç…§ã?—ã?¦ã??ã? ã?•ã?„。
RETURNING SEQUENCE
çœ?ç•¥å?¯èƒ½ã?ªã?“ã?®ã‚­ãƒ¼ãƒ¯ãƒ¼ãƒ‰ã?«ã‚ˆã‚Šã€?Derbyã?®XMLQUERY演算å­?ã?«ã?¦å”¯ä¸€è¿”ã?™ã?“ã?¨ã?®ã?§ã??ã‚‹XMLã?®ç¨®é¡žã‚’記述ã?—ã?¾ã?™ã€‚SEQUENCEã?¯æ—¢å®šã?®è¿”å?´å€¤ã?®ç¨®é¡žã?ªã?®ã?§ã€?ã?“ã?®ã‚­ãƒ¼ãƒ¯ãƒ¼ãƒ‰ã?®ã?‚ã‚‹ã?ªã?—ã?«ã?‹ã?‹ã‚?らã?šã€?XMLQUERY演算å­?ã?¯å?Œæ§˜ã?«æŒ¯èˆžã?„ã?¾ã?™ã€‚ä»–ã?®XMLè¿”å?´å€¤ã?®ç¨®é¡žã?«ã?¤ã?„ã?¦ã?®ã‚ˆã‚Šè©³ç´°ã?ªæƒ…å ±ã?¯ã€?SQL/XML仕様をå?‚ç…§ã?—ã?¦ã??ã? ã?•ã?„。
EMPTY ON EMPTY
çœ?ç•¥ã?§ã??ã?ªã?„ã?“ã?®ã‚­ãƒ¼ãƒ¯ãƒ¼ãƒ‰ã?«ã‚ˆã‚Šã€?XMLQUERY演算å­?ã?®çµ?æžœã?Œç©ºã?®ä¸¦ã?³ã?§ã?‚ã?£ã?Ÿæ™‚ã?®æŒ¯ã‚‹èˆžã?„ã?Œè¨˜è¿°ã?•れã?¾ã?™ã€‚XMLQUERY演算å­?ã?¯ç©ºã?®ä¸¦ã?³ã‚’ã??ã?®ã?¾ã?¾è¿”ã?—ã?¾ã?™ã€‚XMLQUERY演算å­?ã?¯null値ã?«å¤‰æ?›ã?—ã?¾ã?›ã‚“。空ã?®ä¸¦ã?³ã?¯ç›´åˆ—化ã?•れるã?¨ç©ºæ–‡å­—列ã?«ã?ªã‚Šã?¾ã?™ã€‚Derbyã?¯ã€?空ã?®ä¸¦ã?³ã‚’æ•´å½¢å¼?ã?®XML文書ã?¨ã?—ã?¾ã?›ã‚“。

XMLQUERY演算å­?ã?®çµ?æžœã?¯ã€?XMLåž‹ã?®å€¤ã?§ã?™ã€‚ çµ?æžœã?¯XMLノードや値ã?®ä¸¦ã?³ã‚’表ã?—ã?¾ã?™ã€‚文字列ã?®ã‚ˆã?†ã?ªå€¤ã?¯ã€?çµ?æžœã?®ä¸¦ã?³ã?®ä¸€éƒ¨ã?¨ã?ªã‚‹ã?“ã?¨ã?Œã?§ã??ã?¾ã?™ã€‚XMLQUERY演算å­?ã?®çµ?æžœã?¯æ•´å½¢å¼?ã?®XML文書ã?§ã?‚ã‚‹ã?“ã?¨ã?Œä¿?éšœã?•れã?šã€?XML列ã?«XMLQUERY演算å­?ã?®çµ?果を挿入ã?§ã??ã?ªã?„ã?“ã?¨ã?Œã?‚りã?¾ã?™ã€‚ XML列ã?«çµ?果を記録ã?™ã‚‹ã?Ÿã‚?ã?«ã?¯ã€?ã??ã?®çµ?æžœã?¯å”¯ä¸€ã?¤ã?®é …目をæŒ?ã?¤ä¸¦ã?³ã?§ã€?ã??ã?®é …ç›®ã?Œæ•´å½¢å¼?ã?®æ–‡æ›¸ãƒŽãƒ¼ãƒ‰ã?§ã?ªã?‘れã?°ã?ªã‚Šã?¾ã?›ã‚“。 çµ?æžœã?¯XMLSERIALIZE 演算å­?ã?«ã‚ˆã?£ã?¦ç›´åˆ—化ã?™ã‚‹ã?“ã?¨ã?«ã‚ˆã?£ã?¦ã?®ã?¿è¦‹ã‚‰ã‚Œã‚‹ã‚ˆã?†ã?«ã?§ã??ã?¾ã?™ã€‚

例

x_table表ã?«ã?¦ã€?xcolã?¨ã?„ã?†XML列を検索ã?—ã?¦ã€?age属性ã?Œ20より大ã??ã?„studentsè¦?ç´ ã‚’è¿”ã?™ã?«ã?¯æ¬¡ã?®æ–‡ã‚’書ã??ã?¾ã?™ã€‚
SELECT ID,
    XMLSERIALIZE(
        XMLQUERY('//student[@age>20]' PASSING BY REF xcol EMPTY ON EMPTY)
    AS VARCHAR(50))
FROM x_table

��������XMLQUERY演算��実際��果を返��������ら���果��x_table�全行毎��果行��り��。
x_table表ã?«ã?¦xcolã?¨ã?„ã?†XML列を検索ã?—ã?¦ã€?BCã?¨ã?„ã?†å??å‰?ã?®ç”Ÿå¾’ã?®å¹´é½¢ã‚’è¿”ã?™ã?«ã?¯ã€?次ã?®æ–‡ã‚’書ã??ã?¾ã?™ã€‚
SELECT ID, 
    XMLSERIALIZE(
        XMLQUERY('string(//student[text() = "BC"]/@age)' PASSING BY REF xcol EMPTY ON EMPTY)
    AS VARCHAR(50))
FROM x_table
WHERE
    XMLEXISTS('//student[text() = "BC"]' PASSING BY REF xcol)
ã?“ã?®å•?å?ˆã?›ã?§ã?¯çµ?æžœã?«ã?¯ã€?x_table中ã?®BCã?¨ã?„ã?†å??å‰?ã?®ç”Ÿå¾’ã?®è¡Œã?®ã?¿ã?‚りã?¾ã?™ã€‚

利用上�覚書

Derby�XML�機能を動作��る����Apache Xerces�よ��JAXP構文解�器�Apache Xalan�Java�クラスパス�記述�れ��る必���り��。JAXP構文解�器�Xalan�何れ��クラスパス�記述�れ����場���XMLQUERY演算�を利用�よ���る�エラー�発生���。