86juiVSE

1<document>
2<site>  
3<url>htp://www.abc.com/</url>
4<category>Sports</category>
5<usercount>120</usercount>  
6<review>good site</review>
7</site> 
8<site>
9<url>http://www.fb.com/</url>
10<category>Social</category>
11<usercount>100</usercount>  
12<review>Addictive</review>
13</site> 
14<site>  
15<url>http://www.google.com/</url>
16<category>Web Search</category>
17<usercount>1000</usercount>  
18<review>helpful</review>
19</site> 
20</document>
21	
22create table IF NOT EXISTS xmltest(url STRING,category STRING,usercount STRING,review STRING)
23ROW FORMAT SERDE 'com.ibm.spss.hive.serde2.xml.XmlSerDe'
24WITH SERDEPROPERTIES (
25"column.xpath.url"="/document/site/url/text()",
26"column.xpath.category"="/document/site/category/text()",
27"column.xpath.usercount"="/document/site/usercount/text()",
28"column.xpath.review"="/document/site/review/text()")
29STORED AS
30INPUTFORMAT 'com.ibm.spss.hive.serde2.xml.XmlInputFormat'
31OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'
32TBLPROPERTIES (
33"xmlinput.start"="<document",
34"xmlinput.end"="</document>");
35	
36'<string>htp://www.abc.com/http://www.fb.com/http://www.google.com/</string>    <string>SportsSocialWeb Search</string> <string>1201BillionSeveral Billions</string>    <string>good siteAddictiveVery helpful</string>'