Return Data in XML Schema

  • I have been asked to write some code that will extract data and bung it into an XML file. I understand the basics of XML and the For xml clause, what i dont undersdtand is how you get it to use a specific schema. The xml file produced must use this schema so how does SQL server integrate schema documents?

    Any help would be greatly appreciated.

     

    Thanks,

     Jules

    www.sql-library.com[/url]

  • Assuming you are on SQL2000 the answer is "rather poorly". By careful construction you can make the XML match your schema (using EXPLICIT option of FOR XML - beware this is a horror!). You can add nodes to the EXPLICIT xml that are the schema references in the XML - e.g.

    DECLARE @SchemaNamespace varchar(80), @SchemaFile varchar(80), @RCount int

    SELECT @SchemaNamespace = 'http://www.workflowconsulting.co.uk/ATEXML',@SchemaFile = 'ATEInvoices.xsd'

    ....

    SELECT

    10 AS TAG,

    0 AS Parent,

    NULL AS [RootElementName!10!element],

    @SchemaNamespace AS [RootElementName!10!xmlns],

    'http://www.w3.org/2001/XMLSchema-instance' AS [RootElementName!10!xmlns:xsi],

    @SchemaNamespace + ' ' + @SchemaFile AS [RootElementName!10!xsi:schemaLocation],

    .... Nulls for all the elements in your EXPLICIT select then UNION ALL to real selects

    If all this is looking rather complicated - that is because it is! Haven't looked into it but it is likely SQL 2005 has better options as XML support has increased.

    Often you will find it is simpler just to get some pretty raw XML from SQL 2000 and then use and xslt transform to squeeze it into the format needed for your schema

  • As the previous poster mentioned in 2000 this is not easy. we have several situations where we need to format data according to an xml schema and output as a file. We go about doing this using a dts package. In the package we use an activeX script to extract the xml from the DB to a file using adodb.connection,  adodb.command and a adodb.stream objects. see an edited extract below...

    We using XML Explicit because it gives you the most control over xml generation and you don't need to rely on any assumptions. We also validate the xml against the schema file in another activex script using msxml.

      '

      'Create a connection

      '

      ON ERROR RESUME NEXT

      SET oConn = CreateObject("adodb.connection")

      IF Err.Number <> 0 THEN sErrorText = "Failed to create ADO.connection:" & CStr(Err.Number) & vbNewLine & "Source:" & Err.Source & vbNewLine & "Description:" & Err.Description 

      ON ERROR GOTO 0

      IF IsObject(oConn) THEN

     

       '

       'Try to open the connection

       '

       ON ERROR RESUME NEXT

       oConn.Open sConn

       IF Err.Number <> 0 THEN sErrorText = "Failed to create open connection:" & CStr(Err.Number) & vbNewLine & "Source:" & Err.Source & vbNewLine & "Description:" & Err.Description 

       ON ERROR GOTO 0

       IF sErrorText = "" THEN

     

        '

        'Create a command

        '

        ON ERROR RESUME NEXT

        SET oCmd = CreateObject("adodb.command")

        IF Err.Number <> 0 THEN sErrorText = "Failed to create ADO.command:" & CStr(Err.Number) & vbNewLine & "Source:" & Err.Source & vbNewLine & "Description:" & Err.Description 

        ON ERROR GOTO 0

        IF IsObject(oCmd) THEN  

         

         oCmd.CommandText = "<name of stored proc>"

         oCmd.CommandType = adCmdStoredProc

         Set oCmd.ActiveConnection = oConn

         oCmd.CommandTimeout = DTSGlobalVariables("ExtractTimeout").Value

        

         '

         'Create Stream used to extract XML

         '

         ON ERROR RESUME NEXT

         SET oStreamOut = CreateObject("adodb.stream")

         IF Err.Number <> 0 THEN sErrorText = "Failed to create ADO.stream:" & CStr(Err.Number) & vbNewLine & "Source:" & Err.Source & vbNewLine & "Description:" & Err.Description 

         ON ERROR GOTO 0

         IF IsObject(oStreamOut) THEN  

      

          '

          'Execute stored proc to get data      '

          oStreamOut.Open

          oCmd.Properties("Output Stream") = oStreamOut

          oCmd.Parameters("@BatchNumber") = DTSGlobalVariables("JLBatchNumber").Value

          oCmd.Execute , , adExecuteStream + adCmdStoredProc

       

          '

          'Read XML generated and add the xml version info to the front

          '

          oStreamOut.Position = 0

          sXMLString = oStreamOut.ReadText()

          sXMLString = "<?xml version=""1.0"" encoding=""UTF-8"" ?>" & sXMLString

      

          '

          'Assign this back to stream and save to a file

          '

          DTSGlobalVariables("OrdersFilename") = GetOrdersFileName      

          DTSGlobalVariables("ArchiveFullPath") = GetArchiveFilePath

     

          oStreamOut.Position = 0

          oStreamOut.SetEOS

          oStreamOut.WriteText sXMLString, adWriteChar

          oStreamOut.SaveToFile DTSGlobalVariables("ArchiveFullPath").Value, adSaveCreateOverWrite

     

          On Error Resume Next   

          DTSPackageLog.WriteTaskRecord 1, "XML successfully extracted."

          On Error Goto 0

     

     

          lReturnStatus = DTSTaskExecResult_Success

      

         

         ELSE

          'Failed to create command object

          lErrorNo = vbObjectError + 50

         END IF

     

        ELSE

         'Failed to create command object

         lErrorNo = vbObjectError + 40

      

        END IF

      

        '

        'Close Connection

        '

        oConn.Close

     

       ELSE

        'Failed to open connection

        lErrorNo = vbObjectError + 30

       END IF

     

      ELSE

       'Failed to create ado.connection object

       lErrorNo = vbObjectError + 20

      END IF

     

      Set oConn = Nothing

      Set oCmd = Nothing

      Set oStreamOut = Nothing  

    I hope this points you in the correct direction.


    Thanks Jeet

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply