除了string类型外,没有其他数据类型能够转换为XML,XML不能应用与group by语句,也不能是主键或者外键的一部分。
raw、path都可以通过raw('…')、path(’…‘)来指定生成后的row名称,但auto不可以。
query():用于查询XML中节点,返回XML类型
declare @a xml
set @a=
'
<学校>
<班级>
<学生>
<姓名>lyy</姓名>
</学生>
</班级>
</学校>
'
select @a.query('/学校/班级/学生')
结果为:
<学生>
<姓名>lyy</姓名>
for xml:在select中使用该子句可以将查询的结果自动生成xml格式,支持四种模式:raw、auto、explicit、path;
例如:select UserId,UserName from [User] for xml raw
结果:
<row UserId="1" UserName="123 " />
<row UserId="2" UserName="321 " />
<row UserId="3" UserName="123 " />
<row UserId="58" UserName="85 " />
<row UserId="123" UserName="123 " />
例如:select UserId,UserName from [User] for xml raw('user'),root('xuexiao'),elements
结果:
<xuexiao><user>
<UserId>1</UserId>
<UserName>123 </UserName>
</user>
<user>
<UserId>2</UserId>
<UserName>321 </UserName>
</user>
<user>
<UserId>3</UserId>
<UserName>123 </UserName>
</user>
<user>
<UserId>58</UserId>
<UserName>85 </UserName>
</user>
<user>
<UserId>123</UserId>
<UserName>123 </UserName>
</user>
</xuexiao>
例如:select '{'+convert(nvarchar,UserId)+','+isnull(UserName,'null')+'}' from [User] for xml raw(''),elements
结果:
{1,123 }{2,321 }{3,123 }{58,85 }{123,123 }例如:select UserId,UserName from [User] for xml auto
结果:
<User UserId="1" UserName="123 " />
<User UserId="2" UserName="321 " />
<User UserId="3" UserName="123 " />
<User UserId="58" UserName="85 " />
<User UserId="123" UserName="123 " />
例如:select b.UserId,c.UserName from [User] as b join [a] as c on b.UserId=c.UserId for xml auto
结果:
<b UserId="1">
<c UserName="123 " />
</b>
<b UserId="2">
<c UserName="321 " />
</b>
<b UserId="3">
<c UserName="123 " />
</b>
例如:select UserId,UserName from [User] for xml path
结果:
<row>
<UserId>1</UserId>
<UserName>123 </UserName>
</row>
<row>
<UserId>2</UserId>
<UserName>321 </UserName>
</row>
<row>
<UserId>3</UserId>
<UserName>123 </UserName>
</row>
<row>
<UserId>58</UserId>
<UserName>85 </UserName>
</row>
<row>
<UserId>123</UserId>
<UserName>123 </UserName>
</row>
例如:select UserId as '@id',UserName from [User] for xml path
结果:
<row id="1">
<UserName>123 </UserName>
</row>
<row id="2">
<UserName>321 </UserName>
</row>
<row id="3">
<UserName>123 </UserName>
</row>
<row id="58">
<UserName>85 </UserName>
</row>
<row id="123">
<UserName>123 </UserName>
</row>
例如:select UserId as '@id',UserName as 'xuesheng/name' from [User] for xml path
结果:
<row id="1">
<xuesheng>
<name>123 </name>
</xuesheng>
</row>
<row id="2">
<xuesheng>
<name>321 </name>
</xuesheng>
</row>
<row id="3">
<xuesheng>
<name>123 </name>
</xuesheng>
</row>
<row id="58">
<xuesheng>
<name>85 </name>
</xuesheng>
</row>
<row id="123">
<xuesheng>
<name>123 </name>
</xuesheng>
</row>
type:将查询的结果作为xml数据类型返回,这样可以插入到相应的位置;
例如:select(select UserId,UserName from [User] for xml path,type).query('/row/UserId')
结果:
<UserId>1</UserId>
<UserId>2</UserId>
<UserId>3</UserId>
<UserId>58</UserId>
<UserId>123</UserId>
例如:select UserId as 'xuesheng/id',UserName as 'xuesheng/name' from [User] for xml path
结果:
<row>
<xuesheng>
<id>1</id>
<name>123 </name>
</xuesheng>
</row>
<row>
<xuesheng>
<id>2</id>
<name>321 </name>
</xuesheng>
</row>
<row>
<xuesheng>
<id>3</id>
<name>123 </name>
</xuesheng>
</row>
<row>
<xuesheng>
<id>58</id>
<name>85 </name>
</xuesheng>
</row>
<row>
<xuesheng>
<id>123</id>
<name>123 </name>
</xuesheng>
</row>
例如:select(select UserId as 'xuesheng/id',UserName as 'xuesheng/name' from [User] for xml path,type).query('/row')
结果:
<row>
<xuesheng>
<id>1</id>
<name>123 </name>
</xuesheng>
</row>
<row>
<xuesheng>
<id>2</id>
<name>321 </name>
</xuesheng>
</row>
<row>
<xuesheng>
<id>3</id>
<name>123 </name>
</xuesheng>
</row>
<row>
<xuesheng>
<id>58</id>
<name>85 </name>
</xuesheng>
</row>
<row>
<xuesheng>
<id>123</id>
<name>123 </name>
</xuesheng>
</row>
例如:select(select UserId as 'xuesheng/id',UserName as 'xuesheng/name' from [User] for xml path,type).query('/row/xuesheng')
结果:
<xuesheng>
<id>1</id>
<name>123 </name>
</xuesheng>
<xuesheng>
<id>2</id>
<name>321 </name>
</xuesheng>
<xuesheng>
<id>3</id>
<name>123 </name>
</xuesheng>
<xuesheng>
<id>58</id>
<name>85 </name>
</xuesheng>
<xuesheng>
<id>123</id>
<name>123 </name>
</xuesheng>