查询SQL Server 2014中的XML数据

Question

用下面的代码我试图将一个xml文档引入到SQL Server Management Studio中。代码运行，但在结果页面中，行数据全部为NULL。下面是代码：

declare @xml xml 

select @xml=d 
from openrowset (bulk 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\NYairData.xml', single_blob) as data(d) 

declare @hdoc int 

exec sp_xml_preparedocument @hdoc output, @xml 

select * 
from openxml (@hdoc,'response/row/row',1) 
with (
    _id varchar(100), 
    indicator_data_id int, 
    indicator_id int, 
    measure varchar(1000), 
    geo_type_name varchar(200), 
    geo_entity_id int, 
    geo_entity_name varchar(100), 
    year_description int, 
    data_valuemessage float) 

exec sp_xml_removedocument @hdoc 

这里一些XML数据我想使用的：

<response> 
    <row> 
    <row _id="1" _uuid="FDE5AC30-B86A-47C5-9A82-9333398F7898" _position="1" _address="http://data.cityofnewyork.us/resource/c3uy-2p5r/1"> 
     <indicator_data_id>130728</indicator_data_id> 
     <indicator_id>646</indicator_id> 
     <name> 
      Air Toxics Concentrations- Average Benzene Concentrations 
     </name> 
     <measure>Average Concentration</measure> 
     <geo_type_name>Borough</geo_type_name> 
     <geo_entity_id>1</geo_entity_id> 
     <geo_entity_name>Bronx</geo_entity_name> 
     <year_description>2005</year_description> 
     <data_valuemessage>2.8</data_valuemessage> 
    </row> 

的数据是从纽约开放数据的网站。这是一个链接到源网站：https://data.cityofnewyork.us/Environment/Air-Quality/c3uy-2p5r。我是将XML数据引入DBMS的新手。以下是输出的屏幕截图：

Answer 1

阅读OPEN XML文档并查看其中的示例。

另一个好读：XML Elements vs. Attributes

select * 
from openxml (@hdoc,'response/row/row',2) -- 2 = Use the element-centric mapping. 
with (
    _id varchar(100) './@_id', -- ColPattern to map _id attribute 
    indicator_data_id int, 
    indicator_id int, 
    measure varchar(1000), 
    geo_type_name varchar(200), 
    geo_entity_id int, 
    geo_entity_name varchar(100), 
    year_description int, 
    data_valuemessage float)