January 3, 2006 at 3:09 am
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
January 4, 2006 at 2:06 am
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
James Horsley
Workflow Consulting Limited
January 4, 2006 at 6:53 am
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