1CREATE EXTENSION xml2; 2select query_to_xml('select 1 as x',true,false,''); 3 query_to_xml 4--------------------------------------------------------------- 5 <table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+ 6 + 7 <row> + 8 <x>1</x> + 9 </row> + 10 + 11 </table> + 12 13(1 row) 14 15select xslt_process( query_to_xml('select x from generate_series(1,5) as 16x',true,false,'')::text, 17$$<xsl:stylesheet version="1.0" 18 xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> 19<xsl:output method="xml" indent="yes" /> 20<xsl:template match="*"> 21 <xsl:copy> 22 <xsl:copy-of select="@*" /> 23 <xsl:apply-templates /> 24 </xsl:copy> 25</xsl:template> 26<xsl:template match="comment()|processing-instruction()"> 27 <xsl:copy /> 28</xsl:template> 29</xsl:stylesheet> 30$$::text); 31 xslt_process 32--------------------------------------------------------------- 33 <?xml version="1.0"?> + 34 <table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+ 35 + 36 <row> + 37 <x>1</x> + 38 </row> + 39 + 40 <row> + 41 <x>2</x> + 42 </row> + 43 + 44 <row> + 45 <x>3</x> + 46 </row> + 47 + 48 <row> + 49 <x>4</x> + 50 </row> + 51 + 52 <row> + 53 <x>5</x> + 54 </row> + 55 + 56 </table> + 57 58(1 row) 59 60CREATE TABLE xpath_test (id integer NOT NULL, t xml); 61INSERT INTO xpath_test VALUES (1, '<doc><int>1</int></doc>'); 62SELECT * FROM xpath_table('id', 't', 'xpath_test', '/doc/int', 'true') 63as t(id int4); 64 id 65---- 66(0 rows) 67 68SELECT * FROM xpath_table('id', 't', 'xpath_test', '/doc/int', 'true') 69as t(id int4, doc int4); 70 id | doc 71----+----- 72 1 | 1 73(1 row) 74 75DROP TABLE xpath_test; 76CREATE TABLE xpath_test (id integer NOT NULL, t text); 77INSERT INTO xpath_test VALUES (1, '<doc><int>1</int></doc>'); 78SELECT * FROM xpath_table('id', 't', 'xpath_test', '/doc/int', 'true') 79as t(id int4); 80 id 81---- 82(0 rows) 83 84SELECT * FROM xpath_table('id', 't', 'xpath_test', '/doc/int', 'true') 85as t(id int4, doc int4); 86 id | doc 87----+----- 88 1 | 1 89(1 row) 90 91create table articles (article_id integer, article_xml xml, date_entered date); 92insert into articles (article_id, article_xml, date_entered) 93values (2, '<article><author>test</author><pages>37</pages></article>', now()); 94SELECT * FROM 95xpath_table('article_id', 96 'article_xml', 97 'articles', 98 '/article/author|/article/pages|/article/title', 99 'date_entered > ''2003-01-01'' ') 100AS t(article_id integer, author text, page_count integer, title text); 101 article_id | author | page_count | title 102------------+--------+------------+------- 103 2 | test | 37 | 104(1 row) 105 106-- this used to fail when invoked a second time 107select xslt_process('<aaa/>',$$<xsl:stylesheet version="1.0" 108xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> 109<xsl:template match="@*|node()"> 110 <xsl:copy> 111 <xsl:apply-templates select="@*|node()"/> 112 </xsl:copy> 113 </xsl:template> 114</xsl:stylesheet>$$)::xml; 115 xslt_process 116-------------- 117 <aaa/> + 118 119(1 row) 120 121select xslt_process('<aaa/>',$$<xsl:stylesheet version="1.0" 122xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> 123<xsl:template match="@*|node()"> 124 <xsl:copy> 125 <xsl:apply-templates select="@*|node()"/> 126 </xsl:copy> 127 </xsl:template> 128</xsl:stylesheet>$$)::xml; 129 xslt_process 130-------------- 131 <aaa/> + 132 133(1 row) 134 135create table t1 (id integer, xml_data xml); 136insert into t1 (id, xml_data) 137values 138(1, '<attributes><attribute name="attr_1">Some 139Value</attribute></attributes>'); 140create index idx_xpath on t1 ( xpath_string 141('/attributes/attribute[@name="attr_1"]/text()', xml_data::text)); 142SELECT xslt_process('<employee><name>cim</name><age>30</age><pay>400</pay></employee>'::text, $$<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"> 143 <xsl:output method="xml" omit-xml-declaration="yes" indent="yes"/> 144 <xsl:strip-space elements="*"/> 145 <xsl:param name="n1"/> 146 <xsl:param name="n2"/> 147 <xsl:param name="n3"/> 148 <xsl:param name="n4"/> 149 <xsl:param name="n5" select="'me'"/> 150 <xsl:template match="*"> 151 <xsl:element name="samples"> 152 <xsl:element name="sample"> 153 <xsl:value-of select="$n1"/> 154 </xsl:element> 155 <xsl:element name="sample"> 156 <xsl:value-of select="$n2"/> 157 </xsl:element> 158 <xsl:element name="sample"> 159 <xsl:value-of select="$n3"/> 160 </xsl:element> 161 <xsl:element name="sample"> 162 <xsl:value-of select="$n4"/> 163 </xsl:element> 164 <xsl:element name="sample"> 165 <xsl:value-of select="$n5"/> 166 </xsl:element> 167 <xsl:element name="sample"> 168 <xsl:value-of select="$n6"/> 169 </xsl:element> 170 <xsl:element name="sample"> 171 <xsl:value-of select="$n7"/> 172 </xsl:element> 173 <xsl:element name="sample"> 174 <xsl:value-of select="$n8"/> 175 </xsl:element> 176 <xsl:element name="sample"> 177 <xsl:value-of select="$n9"/> 178 </xsl:element> 179 <xsl:element name="sample"> 180 <xsl:value-of select="$n10"/> 181 </xsl:element> 182 <xsl:element name="sample"> 183 <xsl:value-of select="$n11"/> 184 </xsl:element> 185 <xsl:element name="sample"> 186 <xsl:value-of select="$n12"/> 187 </xsl:element> 188 </xsl:element> 189 </xsl:template> 190</xsl:stylesheet>$$::text, 'n1="v1",n2="v2",n3="v3",n4="v4",n5="v5",n6="v6",n7="v7",n8="v8",n9="v9",n10="v10",n11="v11",n12="v12"'::text); 191 xslt_process 192------------------------ 193 <samples> + 194 <sample>v1</sample> + 195 <sample>v2</sample> + 196 <sample>v3</sample> + 197 <sample>v4</sample> + 198 <sample>v5</sample> + 199 <sample>v6</sample> + 200 <sample>v7</sample> + 201 <sample>v8</sample> + 202 <sample>v9</sample> + 203 <sample>v10</sample>+ 204 <sample>v11</sample>+ 205 <sample>v12</sample>+ 206 </samples> + 207 208(1 row) 209 210-- possible security exploit 211SELECT xslt_process('<xml><foo>Hello from XML</foo></xml>', 212$$<xsl:stylesheet version="1.0" 213 xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 214 xmlns:sax="http://icl.com/saxon" 215 extension-element-prefixes="sax"> 216 217 <xsl:template match="//foo"> 218 <sax:output href="0wn3d.txt" method="text"> 219 <xsl:value-of select="'0wn3d via xml2 extension and libxslt'"/> 220 <xsl:apply-templates/> 221 </sax:output> 222 </xsl:template> 223</xsl:stylesheet>$$); 224ERROR: failed to apply stylesheet 225