Friday, March 9, 2012

FOR XML in web page- OK what's next



(1) I need to select records from a SQL database and create a XML document which I then need to write to a users directory. I am using SQL express with VWD 2005. I located the FOR XML and can execute in VWD's SQL graphical tool. I haven't tried in a Web Form yet but I assume it will work ok. But then how do I write the xml results from the SQL query to local user's directory?

(2) I can execute the FOR XML in the SQL graphical tool but how do i Connect to the DB in the Web Form? Use data.sqlClient.Connection and use .SQLcommand to perform the SQL query? If so, then what?

Following is an example I found that shows use of a NameSpace

WITH XMLNAMESPACES (DEFAULT 'urn:example.com/doc'

, 'urn:example.com/customer' as "c"

, 'urn:example.com/order' as"o"

)

SELECT CustomerID as "@.ID",

(SELECT OrderID as "@.OrderID"

from Orders

where Customers.CustomerID=Orders.CustomerID

FOR XML PATH('o:Order'), TYPE

) as "c:Orders",

CompanyName as "c:CompanyName",

ContactTitle as "c:ContactName/@.ContactTitle",

ContactName as "c:ContactName/text()",

PostalCode as "c:Address/@.ZIP",

Address as "c:Address/c:Street",

City as "c:Address/c:City"

FROM Customers

FOR XML PATH('c:Customer'), ROOT('doc')

My research turns up nothing on the above. Is FOR SQL the best way to go? I see that the SQLXML is not available in SQL server express 2005.

Thanks for any help.

Pauley

Once you have the data in a dataset you can write the XML to a file(froma datatable as well if you want) Link is here;

http://msdn2.microsoft.com/en-us/library/zx8h06sz.aspx

To do this you do not need to use for xml or even the XML-DT on the server.

No comments:

Post a Comment