Sql Sever 查询语句生成xml文件
1、AUTO模式:返回数据表为起表名的元素,每一列的值返回为属性;
例如:
SELECT * FROM S_City for xml auto
<S_City CityID="1" CityName="北京市" ZipCode="100000" ProvinceID="1" DateCreated="2008-01-19T19:58:03.173" DateUpdated="2008-01-19T19:58:03.173" />
<S_City CityID="2" CityName="天津市" ZipCode="100000" ProvinceID="2" DateCreated="2008-01-19T19:58:03.173" DateUpdated="2008-01-19T19:58:03.173" />
<S_City CityID="3" CityName="石家庄市" ZipCode="050000" ProvinceID="3" DateCreated="2008-01-19T19:58:03.173" DateUpdated="2008-01-19T19:58:03.173" />
2、RAW模式:返回数据行为元素,每一列的值作为元素的属性;
例如:
select CityID,CityName,ProvinceID from S_City for xml raw('City' )
<City CityID="1" CityName="北京市" ProvinceID="1" />
<City CityID="2" CityName="天津市" ProvinceID="2" />
<City CityID="3" CityName="石家庄市" ProvinceID="3" />
3、PATH模式:通过简单的XPath语法来允许用户自定义嵌套的XML结构、元素、属性值
例如:
select CityID,CityName,ProvinceID from S_City for xml raw('City' )
<City>
<CityID>1 </CityID>
<CityName>北京市</CityName>
<ProvinceID>1 </ProvinceID>
</City>
<City>
<CityID>2 </CityID>
<CityName>天津市</CityName>
<ProvinceID>2 </ProvinceID>
</City>
<City>
<CityID>3 </CityID>
<CityName>石家庄市</CityName>
<ProvinceID>3 </ProvinceID>
</City>