sql 字符窜拼接（关键字 for xml path(''")）/sql读取xml方法介绍/OPENXML 读取节点和节点中的属性

create table student
(
student_name varchar(50),
course_name varchar(50)
)
insert into student values('张三' ,'英语' )
insert into student values('李四', '语文' )
insert into student values('李四' ,'英语' )
insert into student values('王五' ,'英语')
insert into student values('张三' ,'数学' )
select * from student

select student_name,(select course_name+',' from student where student_name=c.student_name for xml path('')) as names into #temp from student c group by student_name
select student_name,SUBSTRING(names,0,LEN(names)) as name from #temp
drop table #temp
drop table student

——————————————————————————————————————————————————————————————————————————————————————-

declare @idoc int
declare @doc varchar(1000)
set @doc ='
<ROOT><Customer CustomerID="VINEsT" ContactName="Paul Henriot">
<Order OrderID="10248" CustomerID="VINET" EmployeeID="5"   OrderDate="1996-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"   OrderDate="1996-08-16T00:00:00">
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
--Create an internal representation of the XML document.
exec sp_xml_preparedocument @idoc OUTPUT, @doc
-- SELECT stmt using OPENXML rowset provider
--aa映射到节点CustomerID cc映射到节点ContactName
--/ROOT/Customer/Order/OrderDetail这个是一层一层的查找
SELECT *FROM   OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2)
WITH (OrderID     int           '../@OrderID',
      CustomerID varchar(10)   '../@CustomerID',
      OrderDate   datetime      '../@OrderDate',
      ProdID      int           '@ProductID',
      Qty         int           '@Quantity',
      aa          varchar(100) 'http://www.cnblogs.com/@CustomerID',
      cc          varchar(100) '../../@ContactName')

------------------------------------------------------------------------------------------------------------------------------------------------------------------

OPENXML 读取节点和节点中的属性（要加@符号），这个是要映射到所属节点

<?xml version='1.0' encoding='gb2312'?><root><record><Dept id='ddd'><![CDATA[test2]]></Dept></record><record><Dept><![CDATA[test3]]></Dept></record></root>

OPENXML (@idoc, '/root/record,2) with(text（节点内容） varchar(300) 'dept',id varchar(300) 'dept/@id')

读取节点：OPENXML (@idoc, '/root/record,2) with(text（节点内容） varchar(300) 'dept')

读取所属节点：OPENXML (@idoc, '/root/record,2) with(text（节点内容） varchar(300) 'dept',id varchar(300) 'dept/@id')

或者

OPENXML (@idoc, '/root/record/dept,2) with(text（节点内容） varchar(300) '../dept',id varchar(300) '@id')

读取节点：OPENXML (@idoc, '/root/record/dept,2) with(text（节点内容） varchar(300) '../dept')

读取所属节点：OPENXML (@idoc, '/root/record/dept,2) with(text（节点内容） varchar(300) '../dept',id varchar(300) '@id')