DB2多行转一行【XML2CLOB方式】

场景：
table1和table2是一对多关系，想要在同一条SQL中把table1和table2的数据全部查出来

方案：
通过DB2的XML2CLOB将多的表合并成一个column，然后在结果中通过Java代码解析成集合

SELECT T1.ID, T2.NAME, (SELECT XML2CLOB(XMLAGG(XMLELEMENT(NAME \"X\",T2.CARD_ID))) FROM TABLE2 AS T2 WHERE T2.ID = T1.ID) AS CARDS FROM TABLE1 AS T1

解析的时候

public static List<String> parseXmlObject(Object obj) {
		List<String> values = new ArrayList<String>();
		if (obj != null) {
			StringBuilder sb = new StringBuilder();
			sb.append("<root>").append(obj.toString()).append("</root>");
			String xml = sb.toString();
			try {
				DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
				factory.setNamespaceAware(false);
				Document doc = factory.newDocumentBuilder().parse(new ByteArrayInputStream(xml.getBytes("UTF-8")));
				XPathExpression expr = XPathFactory.newInstance().newXPath().compile("//root/X");
				NodeList result = (NodeList) expr.evaluate(doc, XPathConstants.NODESET);
				for (int i = 0; i < result.getLength(); i++) {
					values.add(result.item(i).getTextContent().trim());
				}
			} catch (Exception e) {
				LOGGER.error("The exception occur:", e);
			}
		}
		return values;
	}