SQL Server Performance Forum – Threads Archive

How to Create a XML Document?

Hi guys,
I have a problem which is as follows……………
I have to create a XML document on disc. The data comes from a table.
The document is saved as a XML file……
How do I do this…………. ? For example : SELECT Customers.CustomerID, ContactName, CompanyName,
Orders.CustomerID, OrderDate
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
AND (Customers.CustomerID = N’ALFKI’
OR Customers.CustomerID = N’XYZAA’)
ORDER BY Customers.CustomerID
FOR XML AUTO How can I save the output of this in an XML file on Disc?
Can I use sp_xml_preparedoc? If yes then how?
Thanks in advance.
Durgesh.

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=124&rl=1 Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks Ssatya, That link was useful. But i want to create an XML and write the file on ot a disl and then send it as an attachment. This is how i have accomplished it below…………….I hope this also helps others……….. <pre id="code"> SELECT @XMLTextHeader = ‘<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <Batch>' /* WRITE THE HEADER XML IN THE FILE */ EXECUTE @OLEResult = sp_OAMethod @FileID, ‘WriteLine’, Null, @XMLTextHeader IF @OLEResult <> 0 PRINT ‘WriteLine’ SELECT @XMLTextFooter = ‘</Batch>' SELECT @SQLSTR = ” DECLARE CreateXMLText CURSOR LOCAL FOR SELECT TOP 5 ISNULL(O.TestNumberID,0), ISNULL(O.OrderID,0), ISNULL(A.OrderDate,0) , ISNULL(OC.CompanyName,”), ISNULL(OC.City,”), ISNULL((SELECT StateDesc FROM States S WHERE S.StateID = OC.StateID),”) AS State, ISNULL(OC.PostalCode,0) , ISNULL(OC.CompanyName,”), ISNULL(O.Number,”) , ISNULL(OC.Phone,0), ISNULL(A.OrderRef2,”) FROM OCM_CentricVoiceOrder_TestNumbers O INNER JOIN AssetOrder A ON O.OrderID = A.AssetOrderID INNER JOIN OCM_OrderCustomers OC ON OC.OrderID = A.AssetOrderID WHERE UPPER(TestedToLevel3) = ‘NO' OPEN CreateXMLText FETCH NEXT FROM CreateXMLText INTO @Cur_TestNumberID ,@Cur_ExternalOrderID ,@Cur_CustomerRequestedDate , @Cur_BusinessName , @Cur_City , @Cur_State , @Cur_ZipCode , @Cur_CustomerName , @Cur_StartNumber ,@Cur_BillingPhoneNumber ,@Cur_LetterOfAuthDate WHILE (@@FETCH_STATUS = 0) BEGIN –SELECT @SQLSTR = @SQLSTR + SELECT @SQLSTR = ‘<LineItem> <LineItemHeader> <orderAction>Install</orderAction> <orderType>New</orderType> <externalOrderId> ‘+ @Cur_ExternalOrderID +’ </externalOrderId> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />roductCode>2042</productCode> <customerRequestedDate>’+ @Cur_CustomerRequestedDate +’</customerRequestedDate> </LineItemHeader> <Subscriber> <customerTypeIndicator>B</customerTypeIndicator> <businessName>’+ @Cur_BusinessName +’</businessName> <ServiceAddress> <streetNum>402</streetNum> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />reDirectional>E</preDirectional> <streetName>Market</streetName> <streetSuffix>St</streetSuffix> <city>’+ @Cur_City +’</city> <state>’+ @Cur_State +’</state> <zipCode>’+ @Cur_ZipCode +’</zipCode> </ServiceAddress> </Subscriber> <LnpRequest> <DirectoryListingRequest> <directoryListAction>Keep</directoryListAction> </DirectoryListingRequest> <CnamInformation> <blockCustomerName>false</blockCustomerName> <customerName>’+ @Cur_CustomerName +’</customerName> </CnamInformation> <Wtn> <npa>””</npa> <nxx>””</nxx> <tnStartNumber>’[email protected]_StartNumber+’</tnStartNumber> </Wtn> <billingTelephoneNumber>’[email protected]_BillingPhoneNumber+’</billingTelephoneNumber> <lnpLetterOfAuthorization>true</lnpLetterOfAuthorization> <lnpLetterOfAuthorizationDate>’[email protected]_LetterOfAuthDate+’</lnpLetterOfAuthorizationDate> <TestDirection>WirelineToWireline</TestDirection> </LnpRequest> <LineItemResult/> </LineItem>' UPDATE OCM_CentricVoiceOrder_TestNumbers SET TestedToLevel3 = ‘Yes’ WHERE TestNumberID = @Cur_TestNumberID AND UPPER(TestedToLevel3) = ‘NO' /* WRITE THE DATE INTO THE XML FILE */ EXECUTE @OLEResult = sp_OAMethod @FileID, ‘WriteLine’, Null, @SQLSTR IF @OLEResult <> 0 PRINT ‘WriteLine’ FETCH NEXT FROM CreateXMLText INTO @Cur_TestNumberID ,@Cur_ExternalOrderID ,@Cur_CustomerRequestedDate , @Cur_BusinessName , @Cur_City , @Cur_State , @Cur_ZipCode , @Cur_CustomerName , @Cur_StartNumber ,@Cur_BillingPhoneNumber ,@Cur_LetterOfAuthDate END CLOSE CreateXMLText DEALLOCATE CreateXMLText /* IF @SQLSTR <> ” SELECT @SQLSTR = @XMLTextHeader + @SQLSTR + @XMLTextFooter */ /* WRITE THE FOOTER INTO THE XML FILE */ EXECUTE @OLEResult = sp_OAMethod @FileID, ‘WriteLine’, Null, @XMLTextFooter IF @OLEResult <> 0 PRINT ‘WriteLine’ /* CLOSE THE FILE AND FILE HANDLE */ EXECUTE @OLEResult = sp_OADestroy @FileID EXECUTE @OLEResult = sp_OADestroy @FS –PRINT @SQLSTR DECLARE @Rc INT , @subject NVARCHAR(100) , @recipients NVARCHAR(500) SELECT @subject = ‘TestToLevel3 Mail' SELECT @recipients = StringValue FROM TestConfigurations WHERE TestID = 36 AND ConfigurationKey = ‘TestToLevel3Address' IF @SQLSTR <> ” EXEC @Rc = master..xp_sendmail @recipients = @recipients , @message = @subject , @subject = @subject ,@[email protected] IF @Rc <> 0 SELECT ‘Error Sending Mail' </pre id="code"> Durgesh.

Apprecitate your workaround, that can help others. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>