Retrieving XML Data from SQL Server 2000

Questions That Should Be Asked More Frequently

  1. Q. The DataSet, DataTable, and DataColumn objects give me some flexibility in the structure of the XML document I create in the WriteXml method, but I need even more control. I want to add a processing instruction to my XML that references my XSLT transformation. How do I do this?

  2. A. You can use the XmlDataDocument object to access the contents of the DataSet as an XmlDocument. You set the EnforceConstraints property of the DataSet to False and then use the XmlDataDocument object to customize the structure of your XML document as you see fit. In this case, you can use the CreateProcessingInstruction method of the XmlDataDocument object, as shown in the following code snippet. Once you're done, you call the XmlDataDocument object's Save method to create your file.

    Visual Basic .NET

    Dim ds As New DataSet()  ds.EnforceConstraints = False Dim xmlDoc As New XmlDataDocument(ds) Dim strPI as String = "type='text/xsl' href='MyTransform.XSLT'" Dim xmlPI as XmlProcessingInstruction xmlPI = xmlDoc.CreateProcessingInstruction("xml-stylesheet", strPI) xmlDoc.InsertBefore(xmlPI, xmlDoc.DocumentElement) Dim strPathToXmlFile As String = "C:\MyData.XML" xmlDoc.Save(strPathToXmlFile)

    Visual C# .NET

    DataSet ds = new DataSet();  ds.EnforceConstraints = false; XmlDataDocument xmlDoc = new XmlDataDocument(ds); string strPI = "type='text/xsl' href='MyTransform.XSLT'"; XmlProcessingInstruction xmlPI; xmlPI = xmlDoc.CreateProcessingInstruction("xml-stylesheet", strPI); xmlDoc.InsertBefore(xmlPI, xmlDoc.DocumentElement); string strPathToXmlFile = "C:\\MyData.XML"; xmlDoc.Save(strPathToXmlFile);

  3. Q. I want to retrieve the results of my queries as XML, but I'm calling stored procedures that already exist. Is there anything I can do to fetch the results as XML?

  4. A. You can ask the SQL XML .NET Data Provider to format the results of the query as XML by setting the SqlXmlCommand object's ClientSideXml property to True. The following code snippet demonstrates this functionality:

    Visual Basic .NET

    Dim strConn, strSQL As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _           "Initial Catalog=Northwind;Trusted_Connection=Yes;" strSQL = "EXEC CustOrdersOrders 'ALFKI' FOR XML NESTED" Dim cmd As New SqlXmlCommand(strConn) cmd.CommandText = strSQL cmd.ClientSideXml = True cmd.RootTag = "ROOT" Dim xmlDoc As New XmlDocument() Dim xmlRdr As XmlReader = cmd.ExecuteXmlReader xmlDoc.Load(xmlRdr) xmlRdr.Close Console.WriteLine(xmlDoc.InnerXml)

    Visual C# .NET

    string strConn, strSQL; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +           "Initial Catalog=Northwind;Trusted_Connection=Yes;"; strSQL = "EXEC CustOrdersOrders 'ALFKI' FOR XML NESTED"; SqlXmlCommand cmd = new SqlXmlCommand(strConn); cmd.CommandText = strSQL; cmd.ClientSideXml = true; cmd.RootTag = "ROOT"; XmlDocument xmlDoc = new XmlDocument(); XmlReader xmlRdr = cmd.ExecuteXmlReader(); xmlDoc.Load(xmlRdr); xmlRdr.Close(); Console.WriteLine(xmlDoc.InnerXml);

    For more information on using client-side XML formatting with the SQL XML .NET Data Provider, see the "Comparing Client-Side XML Formatting to Server-Side XML Formatting" topic in the SQL XML 3 help file.



Microsoft ADO. NET Core Reference
Microsoft ADO.NET (Core Reference) (PRO-Developer)
ISBN: 0735614237
EAN: 2147483647
Year: 2002
Pages: 104
Authors: David Sceppa

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net