For XML

السلام عليكم ورحمة الله وبركاته :

أخي الكريم :

أولاً : الكتابة في ملف نصي إليك إجرائية الإضافة للملف النصي :

لنفرض أن لديك جدولاً كالتالي :

Create Table Employee_TB(Id Int , Name Nvarchar(50))

ندخل بعض القيم فيه للتجربة( من أجل المثال ) .

Insert Into Employee_TB  Values(1 , 'Ahmad')
Insert Into Employee_TB  Values(12 , 'imad')
Insert Into Employee_TB  Values(123 , 'saleh')
Insert Into Employee_TB  Values(1234 , 'muhammad')

CREATE PROCEDURE sp_AppendToFile(@FileName varchar(255), @Text1 varchar(255)) AS
DECLARE @FS int, @OLEResult int, @FileID int

-- (.. مثلاً IO) للتعامل مع ملفات خارجية  Stream  إنشاء كائن شبيه بالـ  
-- @FS  سوف تعيد مقبض للتعامل مع المجرى وهو هنا Sp_OACreate طبعاً الدالة 

EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject'

-- sp_OAMethod
-- "هده كـ "إفتح الملف Stream سنستخدم الدالة هده لإرسال أمر معين للـ 
--  @FileID مع العملية "فتح ملف" ستعيد مقبض لاستخدامه في الكتابة داخل الملف كما سنرى   sp_OAMethod أيضاً الدالة

execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 8, 1
IF @OLEResult <> 0 PRINT 'OpenTextFile'

--Write Text1
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @Text1
IF @OLEResult <> 0 PRINT 'WriteLine'

-- و لتدمير المجرى sp_OADestroy استخدام الكائن لإغلاق الملف المفتوح 

EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS

ثالثاً: تصدير الجدول Employee_TB إلى الملف المحدد في الإجرائية التالية : C:\T.Txt

Create Proc Export_Employee_Table
AS

Declare @id Nvarchar(50) , @Name Nvarchar(50)
-- to put the value into 
-- them comming from The cursor
Declare @Columns Nvarchar(1000)
-- to combine the columns in a variable

Declare C Cursor For 
 Select Cast(id AS Nvarchar(50)) , Name From Employee_TB
Open C
Fetch Next From C Into @id , @name 
-- fetch values from the record into variables
 While @@Fetch_Status = 0
 Begin
   Set @Columns =  @id + ',' + @Name --  Like :  0025,Ahmad
   Exec sp_AppendToFile @FileName = 'C:\T.Txt' , @Text1 = @Columns 
   -- Calling the procedure sp_AppendToFile to insert the values
Fetch Next From C Into @id , @name 
 End
Close C
DeAllocate C
Go

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

إختبار :

Export_Employee_Table

ثانياً :إليك صفحة من الـ Help تشرح استخدام هذه العبارة بالكامل .. لو بحثت قليلاً لكنت وجدتها :

اقتباس
Guidelines for Using the FOR XML Clause
The FOR XML clause is valid only in the SELECT statement and is subject to these limitations: 

FOR XML is not valid in subselections, whether it is in UPDATE, INSERT, or DELETE statements, a nested SELECT statement, or other statements (SELECT INTO, assignment). For example, subselects as shown in these examples are not supported: 
Example A

SELECT *
FROM Table1
WHERE ......(SELECT * FROM Table2 FOR XML RAW)

Example B

DECLARE @doc nchar(3000)
SET @doc = (SELECT * FROM Customers WHERE CustomerID = 'ALFKI' FOR XML RAW)

FOR XML is not valid for any selection that is used with a COMPUTE BY or FOR BROWSE clause, for example: 
SELECT OrderID, UnitPrice 
FROM [Order Details] 
ORDER BY OrderID COMPUTE SUM(UnitPrice) BY OrderID

GROUP BY and aggregate functions are currently not supported with FOR XML AUTO. For example: 
SELECT max(price), min(price), avg(price)
FROM titles
FOR XML AUTO

FOR XML is not valid in a SELECT statement used in a view definition or in a user-defined function that returns a rowset. For example, this statement is not allowed: 
CREATE VIEW AllOrders AS SELECT * FROM Orders FOR XML AUTO

However, a statement such as the following is allowed:

SELECT * FROM ViewName FOR XML AUTO are allowed.

FOR XML cannot be used in a selection that requires further processing in a stored procedure. 


FOR XML cannot be used with cursors.


Generally, FOR XML cannot be used for any selections that do not produce direct output to the Microsoft® SQL Server™ 2000 client.


FOR XML cannot be used in a stored procedure when called in an INSERT statement.


When a SELECT statement with a FOR XML clause specifies a four-part name in the query, the server name is not returned in the resulting XML document when the query is executed on the local computer. However, the server name is returned as the four-part name when the query is executed on a network server. 
For example, consider this query:

SELECT TOP 1 LastName
FROM ServerName.Northwind.dbo.Employees
FOR XML AUTO

When ServerName is a local server, the query returns:

<Northwind.dbo.Employees LastName="Buchanan"/>

When ServerName is a network server, the query returns:

<ServerName.Northwind.dbo.Employees LastName="Buchanan"/>

This can be avoided by specifying this alias:

SELECT TOP 1 LastName
FROM ServerName.Northwind.dbo.Employees x
FOR XML AUTO 

This query returns:

<x ="Buchanan"/>

Using derived tables in a SELECT statement with FOR XML AUTO may not produce the nesting you want. 
The FOR BROWSE mode is implemented when a query with the FOR XML AUTO mode is specified. The FOR XML AUTO mode uses the information provided by the FOR BROWSE mode in determining the hierarchy in the result set. 

For example, consider the following query. A derived table P is created in the query.

SELECT c.CompanyName,
	   o.OrderID,
	   o.OrderDate,
	   p.ProductName,
	   p.Quantity,
	   p.UnitPrice,
	   p.Total
FROM   Customers AS c
	   JOIN
	   Orders AS o
	   ON
	   c.CustomerID = o.CustomerID
	   JOIN
	   (
		 SELECT od.OrderID,
				pr.ProductName,
				od.Quantity,
				od.UnitPrice,
				od.Quantity * od.UnitPrice AS total
		 FROM   Products AS pr
				JOIN
				[Order Details] AS od
				ON
				pr.ProductID = od.ProductID
	   ) AS p
	   ON
	   o.OrderID = p.OrderID
FOR XML AUTO

This is the partial result:

<c CompanyName="Vins et alcools Chevalier">
  <o OrderID="10248" OrderDate="1996-07-04T00:00:00">
	<pr ProductName="Queso Cabrales">
		<od Quantity="12" UnitPrice="14.0000" total="168.0000"/>
	</pr>
	<pr ProductName="Singaporean Hokkien Fried Mee">
		<od Quantity="10" UnitPrice="9.8000" total="98.0000"/>
	</pr>
</c>

In the resulting XML document, the <p> element is missing, and the <pr> and <od> elements are returned. This occurs because the query optimizer eliminates the P table in the result and returns a result set consisting of the od and pr tables. 

This can be avoided by rewriting the query. For example, you can rewrite the query is to create a view and use it in the SELECT statement:

CREATE VIEW p AS  
		 SELECT od.OrderID,
				pr.ProductName,
				od.Quantity,
				od.UnitPrice,
				od.Quantity * od.UnitPrice AS total
		 FROM   Products AS pr
				JOIN
				[Order Details] AS od
				ON
				pr.ProductID = od.ProductID

And then write the SELECT statement:

SELECT c.CompanyName,
	   o.OrderID,
	   o.OrderDate,
	   p.ProductName,
	   p.Quantity,
	   p.UnitPrice,
	   p.total
FROM   Customers AS c
	   JOIN
	   Orders AS o
	   ON
	   c.CustomerID = o.CustomerID
	   JOIN
		p
	   ON
	   o.OrderID = p.OrderID
FOR XML AUTO

This is the partial result:

<c CompanyName="Vins et alcools Chevalier">
  <o OrderID="10248" OrderDate="1996-07-04T00:00:00">
	<p ProductName="Queso Cabrales" 
	   Quantity="12" 
	   UnitPrice="14.0000" 
	   total="168.0000"/>
  </o>
</c>

In addition, SQL Server names containing characters that are invalid in XML names (such as spaces) are translated into XML names in a way in which the invalid characters are translated into escaped numeric entity encoding.

There are only two nonalphabetic characters that can begin an XML name: the colon (:) and the underscore (_). Because the colon (:) is already reserved for namespaces, the underscore (_) is chosen as the escape character. The escape rules used for encoding are: 

Any UCS-2 character that is not a valid XML name character (according to the XML 1.0 specification) is escaped as _xHHHH_, where HHHH stands for the four-digit hexadecimal UCS-2 code for the character in the most significant bit-first order. For example, the table name Order Details is encoded as Order_x0020_Details.


Characters that do not fit into the UCS-2 realm (the UCS-4 additions of the range U+00010000 to U+0010FFFF) are encoded as _xHHHHHHHH_, where HHHHHHHH stands for the eight-digit hexadecimal UCS-4 encoding of the character.


The underscore character does not need to be escaped unless it is followed by the character x. For example, the table name Order_Details is not encoded.


The colon (:) in identifiers is not escaped so that the namespace element and attribute names can be generated by the FOR XML query. For example, the following query generates a namespace attribute with a colon in the name: 
SELECT 'namespace-urn' as 'xmlns:namespace', 
		 1 as 'namespace:a' 
FOR XML RAW

The query produces this result:

<row xmlns:namespace="namespace-urn" namespace:a="1"/>

In a SELECT query, casting of any column to a binary large object (BLOB) makes it a temporary entity (losing its associated table name and column name). This causes AUTO mode queries to generate an error because it does not know where to place this value in the XML hierarchy, for example: 
CREATE TABLE MyTable (Col1 int PRIMARY KEY, Col2 binary)
INSERT INTO MyTable VALUES (1, 0x7)

This query produces an error because of the casting to a BLOB:

SELECT Col1,
		 CAST(Col2 as image) as Col2
FROM MyTable
FOR XML AUTO

If you remove the casting, the query produces results as expected:

SELECT Col1,
		 Col2
FROM MyTable
FOR XML AUTO

This is the result:

<Computed Col1="1" Col2="dbobject/Computed[@Col1='1']/@Col2"/>

بالتوفيق أخي الكريم :)

الف شكر كل الكلام اللي انتة قلتة ده جميل

بس برده انا عايز احط الXML ده في File XML علي الجهاز

السلام عليكم ورحمة الله وبركاته :

أولاً أخي الكريم لا شكر على واجب ..

بالنسبة لسؤالك فأنا أتوقع أن الإجابة في صفحة الـHelp التي وضعتها في المشاركة ..

و هي أن العبارة For XML لا تستخدم إلا في عبارة الSELECT للعرض

فقط و بالتالي فإن عليك أن تستخدم السجل الناتج عن استعلام الـ FOR XML ضمن لغة البرمجة

التي تعمل بها لأخذ الناتج و تستخدم StreamWriter أو أي طريقة لتكتب السجل الناتج و تحفظ

النتائج في ملف بلاحقة XML ....

الف شكر

يعني مافيش طريقة من خلال الSQL تعمل الموضوع ده خالص