使用sql的xmlpath可以把xml文件转化为表格

DECLARE @xml XML
SET @xml='<urlset content_method="full">
<url>
<loc><![CDATA[http://m.fang.com/esf/gz/DS_569656.html?sf_source=bdfesf]]></loc>
<lastmod><![CDATA[2017-02-23]]></lastmod>
<changefreq>always</changefreq>
<priority><![CDATA[0.9]]></priority>
<data>
<display>
<name><![CDATA[经典居]]></name>
<type>二手房</type>
<site>房天下</site>
<province><![CDATA[广东]]></province>
<city><![CDATA[广州]]></city>
<title><![CDATA[海珠 经典居 2室1厅]]></title>
<detail_url><![CDATA[http://m.fang.com/esf/gz/DS_569656.html?sf_source=bdfesf]]></detail_url>
<wise_detail_url><![CDATA[http://m.fang.com/esf/gz/DS_569656.html?sf_source=bdfesf]]></wise_detail_url>
<housing_url><![CDATA[https://m.fang.com/xiaoqu/gz/2811021817.html?sf_source=bdfesf]]></housing_url>
<img><![CDATA[http://img10.soufunimg.com/m/2016_07/14/M07/07/7E/wKgHDVeHUXmIb85XAAdg4LuNc0UAALtTwO2NEsAB2D4409.jpg]]></img>
<layout><![CDATA[2室1厅]]></layout>
<room_nums><![CDATA[2]]></room_nums>
<floor><![CDATA[高楼层]]></floor>
<area><![CDATA[65.00]]></area>
<orientation><![CDATA[南北]]></orientation>
<tag><![CDATA[随时看房]]></tag>
<tag><![CDATA[房天下自营]]></tag>
<total_price><![CDATA[1500000]]></total_price>
<average_price><![CDATA[23077]]></average_price>
<age><![CDATA[20]]></age>
<hot><![CDATA[171]]></hot>
<district><![CDATA[海珠]]></district>
<bizcircle><![CDATA[滨江东]]></bizcircle>
<house_type><![CDATA[住宅]]></house_type>
<decorate><![CDATA[精装修]]></decorate>
</display>
</data>
</url>
<url>
<loc><![CDATA[http://m.fang.com/esf/gz/DS_412452.html?sf_source=bdfesf]]></loc>
<lastmod><![CDATA[2017-02-23]]></lastmod>
<changefreq>always</changefreq>
<priority><![CDATA[0.6]]></priority>
<data>
<display>
<name><![CDATA[天誉花园二期]]></name>
<type>二手房</type>
<site>房天下</site>
<province><![CDATA[广东]]></province>
<city><![CDATA[广州]]></city>
<title><![CDATA[天河 天誉花园二期 3室2厅]]></title>
<detail_url><![CDATA[http://m.fang.com/esf/gz/DS_412452.html?sf_source=bdfesf]]></detail_url>
<wise_detail_url><![CDATA[http://m.fang.com/esf/gz/DS_412452.html?sf_source=bdfesf]]></wise_detail_url>
<housing_url><![CDATA[https://m.fang.com/xiaoqu/gz/2811656140.html?sf_source=bdfesf]]></housing_url>
<img><![CDATA[http://img1.soufun.com/agents/2009_06/10/gz/houseinfo/1244612608428_000.jpg]]></img>
<layout><![CDATA[3室2厅]]></layout>
<room_nums><![CDATA[3]]></room_nums>
<floor><![CDATA[高楼层]]></floor>
<area><![CDATA[162.00]]></area>
<orientation><![CDATA[朝东]]></orientation>
<tag><![CDATA[满两年]]></tag>
<tag><![CDATA[房天下自营]]></tag>
<total_price><![CDATA[4500000]]></total_price>
<average_price><![CDATA[27778]]></average_price>
<age><![CDATA[15]]></age>
<hot><![CDATA[135]]></hot>
<district><![CDATA[天河]]></district>
<bizcircle><![CDATA[天河北]]></bizcircle>
<house_type><![CDATA[住宅]]></house_type>
<decorate><![CDATA[简装]]></decorate>
</display>
</data>
</url>
</urlset>'


select  T.C.value('name[1]','varchar(max)') as name,
        T.C.value('province[1]','varchar(max)') as province,
        T.C.value('city[1]','varchar(max)') as city,
        T.C.value('title[1]','varchar(max)') as title,
        T.C.value('detail_url[1]','varchar(max)') as detail_url,
        T.C.value('wise_detail_url[1]','varchar(max)') as wise_detail_url,
        T.C.value('housing_url[1]','varchar(max)') as housing_url,
        T.C.value('img[1]','varchar(max)') as img
from    @xml.nodes('/urlset/url/data/display') as T ( C )