xml 관련 함수 예제

1)

WITH x(col) AS (SELECT '<?xml version=1.0 ?><response><status>ERROR_MISSING_DATA</status></response>'::xml)
SELECT xpath('./status/text()', col) AS status
FROM   x;

=>

{ERROR_MISSING_DATA}

2)

WITH x(col) AS (SELECT '<?xml version=1.0 ?><a><x>/1:L</x><x>/2:L</x><x>/3:L</x><x>/11:L</x><x>/12:L</x><x>/13:L</x></a>'::xml)
SELECT unnest(xpath('/a/x/text()', col)) AS status
FROM   x;

=>

/1:L
/2:L
/3:L
/11:L
/12:L
/13:L

3)

WITH x(col) AS (SELECT '<?xml version=1.0 ?><a><x>/1:L</x><x>/2:L</x><x>/3:L</x><x>/11:L</x><x>/12:L</x><x>/13:L</x></a>'::xml)
SELECT ARRAY_TO_STRING(xpath('/a/x/text()', col),'') AS status
FROM   x;

=>

/1:L/2:L/3:L/11:L/12:L/13:L