For XML Path 用法总结？

Question

For XML Path 用法总结？有的贴一下吧，谢谢！

Answer 1

PATH模式： 
　　(1). SQL语句： 　　SELECT EmployeeID "@ID",FirstName "Name/FirstName",LastName "Name/LastName"  
　　FROM Employees FOR XML PATH ('Employee')  
(2). 所生成的XML文件 
<Employee ID="1"> 
<Name> 
<FirstName>NancyFirstName> 
<LastName>DavolioLastName> 
Name> 
Employee> 
<Employee ID="2"> 
<Name> 
<FirstName>AndrewFirstName> 
<LastName>FullerLastName> 
Name> 
Employee> 
<Employee ID="3"> 
<Name> 
<FirstName>JanetFirstName> 
<LastName>LeverlingLastName> 
Name> 
Employee> 
<Employee ID="4"> 
<Name> 
<FirstName>MargaretFirstName> 
<LastName>PeacockLastName> 
Name> 
Employee> 
<Employee ID="5"> 
<Name> 
<FirstName>StevenFirstName> 
<LastName>BuchananLastName> 
Name> 
Employee> 
<Employee ID="6"> 
<Name> 
<FirstName>MichaelFirstName> 
<LastName>SuyamaLastName> 
Name> 
Employee> 
<Employee ID="7"> 
<Name> 
<FirstName>RobertFirstName> 
<LastName>KingLastName> 
Name> 
Employee> 
<Employee ID="8"> 
<Name> 
<FirstName>LauraFirstName> 
<LastName>CallahanLastName> 
Name> 
Employee> 

Answer 2

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, 
      N'<ROOT>
         <Customers CustomerID="XYZAA" ContactName="Joe" 
               CompanyName="Company1">
            <Orders CustomerID="XYZAA" 
               OrderDate="2000-08-25T00:00:00"/>
            <Orders CustomerID="XYZAA" 
               OrderDate="2000-10-03T00:00:00"/>
         </Customers>
         <Customers CustomerID="XYZBB" ContactName="Steve"
               CompanyName="Company2">No Orders yet!
         </Customers>
      </ROOT>'
-- Use OPENXML to provide rowset consisting of customer data.
INSERT Customers 
SELECT * 
FROM OPENXML(@hDoc, N'/ROOT/Customers') 
     WITH Customers
-- Use OPENXML to provide rowset consisting of order data.
INSERT Orders 
SELECT * 
FROM OPENXML(@hDoc, N'//Orders') 
     WITH Orders
-- Using OPENXML in a SELECT statement.
SELECT * FROM OPENXML(@hDoc, N'/ROOT/Customers/Orders') with (CustomerID nchar(5) '../@CustomerID', OrderDate datetime)
-- Remove the internal representation of the XML document.
EXEC sp_xml_removedocument @hDoc
不懂的学习

Answer 3

IF OBJECT_ID('A') IS NOT NULL 
DROP TABLE A
IF OBJECT_ID('B') IS NOT NULL
DROP TABLE B
goCREATE TABLE A
(
AID INT,
[NAME] VARCHAR(50)
)
INSERT INTO A
SELECT 1    ,'Tom' UNION ALL
SELECT 2    ,'Jim' CREATE TABLE B(
BID INT
,[NUMBER] VARCHAR(50)
)
INSERT INTO B
SELECT 1    ,'20' UNION ALL
SELECT 1    ,'30' 
GO
select name ,
number=isnull(stuff((select ','+rtrim(number) from b where a.aid=bid for XML path('')),1,1,''),'')
from A