🚀 go-pugleaf

���SQL Server 2000�ṩ��FOR XML��ѯ��SQL Server 2005�汾�����й�����ǿ�Ļ�
�������˲����¹��ܣ���Ϊ�����˵Ĺ��ܰ�����Xml�������֧�֡�ʹ��PATHģʽ�Լ�Ƕ
��FOR XML��ѯ֧�ֵȣ�����ζ��ͨ���µ�FOR XML��ѯ���ܿ��Թ�����ṹ�������X
ml��ݡ�

��SQL Server 2000��FOR XML��ѯ�Ľ����ֱ�����ı���ʽ���ص��ͻ��ˣ�Ϊ֧��SQL 
Server 2005�е�Xml������ͣ�FOR XML ��ѯ�ṩ��TYPEָ�ͨ��TYPE֧�ַ��ؽ��
�����ΪXml������͡���������SQL Server2005�е�AdventureWorksʾ����ݿ�Ϊ����
���书�ܡ�

��������һ��SQL��ѯ��

DECLARE @TestStringValue nvarchar(1024);

SET @TestStringValue = (SELECT ProductID,[Name] FROM Production.Product

WHERE ProductSubcategoryID = 9

FOR XML AUTO);

SELECT @TestStringValue; 

������

<Production.Product ProductID="894" Name="Rear 
Derailleur"/><Production.Product ProductID="945" Name="Front Derailleur"/> 

���Ϊ�ַ�ֻ�����ݸ�ʽ���XmlƬ��Ҫ�󣬲����޷���SQL Server Management 
Studio�е����Xml��ʽ��ʾ��

�����SQL�����޸ģ�����TYPEָ�

DECLARE @TestStringValue nvarchar(1024);

SET @TestStringValue = (SELECT ProductID,[Name] FROM Production.Product

WHERE ProductSubcategoryID = 9

FOR XML AUTO,TYPE); 

�������⣬��ѯ����յ����´�����ʾ��

��������������xml ��nvarchar ����ʽת������ʹ��CONVERT ���������д˲�ѯ��

�ɼ�ͨ��TYPEָ��ص�����Ѿ�ת���Xml���ͣ��Ľ��IJ�ѯ������£�

DECLARE @XmlTestValue xml;

SET @XmlTestValue = (SELECT ProductID,[Name] FROM Production.Product

WHERE ProductSubcategoryID = 9

FOR XML AUTO,TYPE);

SELECT @XmlTestValue; 

ͨ��SELECT ���صĽ��֧��Xml��ʽ��ʾ��

 

�˽���TYPEָ��Ĺ��ܺ󣬾Ϳ�������FOR XML��ѯ����ߵĸ�ྪϲ����ʾ�����
��IJ�Ʒ�?Production.Product���Ͳ�Ʒ��������?Production.ProductSubcatego
ry��Ϊ�������ʾ��

��д��Ʒ�����Ϊ��9���IJ�Ʒ��ݲ�ѯ��

SELECT ProductSubcategoryID,[Name] ,

(SELECT ProductID,[Name] FROM Production.Product Product

    WHERE ProductSubcategoryID = 9

    FOR XML AUTO,TYPE) AS Products

FROM Production.ProductSubcategory AS Category

WHERE ProductSubcategoryID = 9

FOR XML AUTO; 

���������£�

<CategoryProductSubcategoryID="9"Name="Derailleurs">

 <Products>

    <ProductProductID="894"Name="Rear Derailleur" />

    <ProductProductID="945"Name="Front Derailleur" />

 </Products>

</Category> 

����ڲ�Ʒ��ѯ�Ӿ��в��ṩTYPEָ���ô����ͻ���������

<CategoryProductSubcategoryID="9"Name="Derailleurs"Products="&lt;Product 
ProductID=&quot;894&quot; Name=&quot;Rear Derailleur&quot;/&gt;&lt;Product 
ProductID=&quot;945&quot; Name=&quot;Front Derailleur&quot;/&gt;" /> 

 

���ϼ�����ѯ���û�д�����ռ䣬����SQL Server 2005 ͨ��WITH XMLNAMESPACES
�Ӿ��ṩ�˶�����ռ��֧�֣�����IJ�ѯ����ʾһ������Ĵ�Ĭ������ռ��Xml�ĵ�
��

WITH XMLNAMESPACES ('uri0' as ns0)

SELECT ProductSubcategoryID,[Name] ,

(SELECT ProductID,[Name] FROM Production.Product Product

    WHERE ProductSubcategoryID = 9

    FOR XML RAW('ns0:Product'),TYPE) AS 'ns0:Products'

FROM Production.ProductSubcategory AS Category

WHERE ProductSubcategoryID = 9

FOR XML RAW('ns0:Category'),ROOT('ns0:Message'); 

������

<ns0:Messagexmlns:ns0="uri0">

 <ns0:CategoryProductSubcategoryID="9"Name="Derailleurs">

    <ns0:Products>

      <ns0:Productxmlns:ns0="uri0"ProductID="894"Name="Rear Derailleur" />

      <ns0:Productxmlns:ns0="uri0"ProductID="945"Name="Front Derailleur" />

    </ns0:Products>

 </ns0:Category>

</ns0:Message> 

 

������Ĭ������ռ䣬��дSQL���£�

WITH XMLNAMESPACES (DEFAULT 'http://blog.csdn.net/zhzuo')

SELECT ProductSubcategoryID,[Name] ,

(SELECT ProductID,[Name] FROM Production.Product Product

    WHERE ProductSubcategoryID = 9

    FOR XML AUTO,TYPE) AS Products

FROM Production.ProductSubcategory AS Category

WHERE ProductSubcategoryID = 9

FOR XML AUTO,ROOT('Message'); 

������

<Messagexmlns="http://blog.csdn.net/zhzuo">

 <CategoryProductSubcategoryID="9"Name="Derailleurs">

    <Products>

      <Productxmlns="http://blog.csdn.net/zhzuo"ProductID="894"Name="Rear 
Derailleur" />

      <Productxmlns="http://blog.csdn.net/zhzuo"ProductID="945"Name="Front 
Derailleur" />

    </Products>

 </Category>

</Message> 

 

���⣬��SQL Server 2005��������FOR XML PATH ģʽ��ͨ��ʹ��FOR XML PATH ģʽ��
�Խ�����������Xml��ʽ�����ʹ��FOR XML EXPLICIT��Ϊ�򵥣�

WITH XMLNAMESPACES (DEFAULT 'http://blog.csdn.net/zhzuo')

SELECT ProductID AS 'Product/@ProductID',

    Name AS 'Product/Name'

FROM Production.Product

WHERE ProductSubcategoryID = 9

FOR XML PATH(''),ROOT('Products'); 

������

<Productsxmlns="http://blog.csdn.net/zhzuo">

 <ProductProductID="894">

    <Name>Rear Derailleur</Name>

 </Product>

 <ProductProductID="945">

    <Name>Front Derailleur</Name>

 </Product>

</Products> 

 

��ʵ��Ӧ�ù���У���.NETƽ̨Ӧ�ÿ���Ϊ��ͨ��ADO.NET����SQL Server 2005����
��Xml��ݽṹ���Ը�ӷḻ������������������Xml��ʽת��ֱ�ӷ�������һ��ģ�顣
����ҵӦ�ü��ɷ��棬��BizTalkƽ̨Ϊ���ڵ���SQL Adapter��ʱ����Ը����
����Ϊ���ƶ�����Xml Schema
�����ĸ����ԡ�Ŀǰ�����°汾��BizTalk��2006 R2����ʹ����SQL Server 2005��ǿFO
R XML���ܵĴ洢��̻�SQL��䲻֧��ͨ��SQL�����������XML Schema�����������
�����ֱ��ͨ��Visual Studio IDE��д���ʵ�ʸ�ʽ��XML Schema�������

 
--

�� ��Դ:����ˮ˼Դ bbs.sjtu.edu.cn��[FROM: 59.78.51.96]