tag:blogger.com,1999:blog-3851595309768128250.post3027111788558675135..comments2023-04-12T06:51:09.158-06:00Comments on A_Non On XML: Basic XML Parsing via XMLTableJason Hhttp://www.blogger.com/profile/02172936625260505714noreply@blogger.comBlogger3125tag:blogger.com,1999:blog-3851595309768128250.post-67687066336314799562012-09-22T10:02:41.149-06:002012-09-22T10:02:41.149-06:00Aim to extract the BRAND and its COST and it is ge...Aim to extract the BRAND and its COST and it is getting extracted without any problem using <br /><br />CREATE TABLE xml_tab (seqno number default 1, xml_data XMLType);<br /><br />XMLTable.<br />INSERT INTO xml_tab<br />VALUES<br /> (1, XMLType('<br /> <br /> 1000<br /> 2000<br /> <br /> '));<br /><br />SELECT t.seqno, tx.* FROM xml_tab t,<br /> XMLTABLE('/PRODUCTS/LAPTOPS/NOTEBOOK' PASSING t.xml_data<br /> COLUMNS brandname CHAR(20) PATH '@BRAND',<br /> cost CHAR(20) PATH '.') tx<br />WHERE brandname in ('HP', 'LENOVA') AND seqno = 1;<br /><br />Outpupt<br /> SEQNO BRANDNAME COST<br />---------- -------------------- --------------------<br /> 1 HP 1000<br /> 1 LENOVA 2000<br /><br /><br />Below query will demonstrate the problem I am facing and need your kind help to resovle it.<br /><br />INSERT INTO xml_tab<br />VALUES<br /> (2, XMLType('<br /> <br /> 3000<br /> 1400<br /> 5000<br /> <br /> '));<br /><br />(Below query is not working)<br />SELECT t.seqno, tx.* FROM xml_tab t,<br /> XMLTABLE('/PRODUCTS/LAPTOPS/NOTEBOOK[1]' PASSING t.xml_data<br /> COLUMNS brandname CHAR(20) PATH '@BRAND',<br /> cost CHAR(20) PATH '.') tx<br />WHERE brandname in ('HP', 'LENOVA') AND seqno = 2;<br /><br />My requirement is to display only single row of HP and LENOVA brand even if there are multiple XML tags of one brand. Therefore, desired output is<br /> SEQNO BRANDNAME COST<br />---------- -------------------- --------------------<br /> 2 HP 3000<br /> 2 LENOVA 5000<br /><br />Kindly suggest.<br /><br />Thanks in advance<br />SandeepSandeep Kumarhttps://www.blogger.com/profile/02213147142651981111noreply@blogger.comtag:blogger.com,1999:blog-3851595309768128250.post-32106996903326543662012-09-22T10:01:09.962-06:002012-09-22T10:01:09.962-06:00This comment has been removed by the author.Sandeep Kumarhttps://www.blogger.com/profile/02213147142651981111noreply@blogger.comtag:blogger.com,1999:blog-3851595309768128250.post-1409898044434620392010-11-22T03:59:01.081-07:002010-11-22T03:59:01.081-07:00It's awesome.. It works fine thanksIt's awesome.. It works fine thanksAnonymousnoreply@blogger.com