T-sql for xml path使用(轉)

標籤：dmi   for   count   http   參考   typeid   foreign   logs   www   

參考：

http://www.cnblogs.com/langhua/p/4193161.html

//用法： FOR XML PATH 方法是用於將查詢結果集以XML形式展示sql:

select top 5 p.ContactTypeID,p.ModifiedDate,p.Name from [Person].[ContactType] p for xml path

顯示結果：

<row>  <ContactTypeID>1</ContactTypeID>  <ModifiedDate>2002-06-01T00:00:00</ModifiedDate>  <Name>Accounting Manager</Name></row><row>  <ContactTypeID>2</ContactTypeID>  <ModifiedDate>2002-06-01T00:00:00</ModifiedDate>  <Name>Assistant Sales Agent</Name></row><row>  <ContactTypeID>3</ContactTypeID>  <ModifiedDate>2002-06-01T00:00:00</ModifiedDate>  <Name>Assistant Sales Representative</Name></row><row>  <ContactTypeID>4</ContactTypeID>  <ModifiedDate>2002-06-01T00:00:00</ModifiedDate>  <Name>Coordinator Foreign Markets</Name></row><row>  <ContactTypeID>5</ContactTypeID>  <ModifiedDate>2002-06-01T00:00:00</ModifiedDate>  <Name>Export Administrator</Name></row>

path的用法：用來指定行結點，如果不指定行節點，預設使用‘row‘,

sql:

select top 5 p.ContactTypeID,p.ModifiedDate,p.Name from [Person].[ContactType] p for xml path(‘AA‘)

顯示結果：

<AA>  <ContactTypeID>1</ContactTypeID>  <ModifiedDate>2002-06-01T00:00:00</ModifiedDate>  <Name>Accounting Manager</Name></AA><AA>  <ContactTypeID>2</ContactTypeID>  <ModifiedDate>2002-06-01T00:00:00</ModifiedDate>  <Name>Assistant Sales Agent</Name></AA><AA>  <ContactTypeID>3</ContactTypeID>  <ModifiedDate>2002-06-01T00:00:00</ModifiedDate>  <Name>Assistant Sales Representative</Name></AA><AA>  <ContactTypeID>4</ContactTypeID>  <ModifiedDate>2002-06-01T00:00:00</ModifiedDate>  <Name>Coordinator Foreign Markets</Name></AA><AA>  <ContactTypeID>5</ContactTypeID>  <ModifiedDate>2002-06-01T00:00:00</ModifiedDate>  <Name>Export Administrator</Name></AA>

下來來利用 for xml path 輸出結果就是一行字串，如果去掉行節點就可以把整列的結果輸出一行，

sql : select top 5 ‘[‘+ p.Name+‘]‘ from [Person].[ContactType] p for xml path(‘‘)

顯示結果： 

[Accounting Manager][Assistant Sales Agent][Assistant Sales Representative][Coordinator Foreign Markets][Export Administrator]

T-sql for xml path使用(轉)