Folks,

I would like to know if it is possible to generate an XML file containing data from a database contained within nodes extracted from an XSD.

For example:

I have an xsd which contains references to CarVidNo, CarRegNo, CarMake, CarModel, CarEngine, CarPrice and my system contains a table called Car:

CarVidNo – Number (Key)
CarRegNo – Text
CarMake – Text
CarModel - Text
CarEngine – Text
CarColour – Text
CarOwner – Text
CarPrice – Number

Is it possible to build an xml message (cars.xml) by extracting the element names from the xsd dynamically at runtime, creating the node, taking data from the recordset and placing it into the node and then closing the node.

So, the output looks something like this:

<CarVidNo>3344556677</CarVidNo>

In an ideal world, I would like to avoid hard coding the Element names into the code to make maintenance easier for the future.

Does any know if it is possible to do this? I have read online plenty of stuff about VB and DOM processing and building messages that way, but I havent seen anything which uses an XSD to dynamically (if that is the right word) create an XML message.

All help, suggestions and whatever are suggested.

Thanks,

Morris

Hi Morris,

Since you mention VB and DOM I assume you're getting your data via ADO? If so, have you considered using ADO's built-in support for saving recordsets in XML format (did you know you can save directly from recordset to XML DOM using the save method)? If you don't like the XML format that ADO gives you, there's nothing to stop you running an XSLT over it afterwards to get exactly what you like. The XSLT can be stored in a plain text file, so this is one easy way to get your data into an XML format you want without hard-coding anything.

hth
Phil

Hi Phil!

Sorry - I should have mentioned that I am using ADO to retrieve the data.

I didnt know that ADO had its own XML methods built in. How do these compare to using things like MSXML?

Do you know if it possible to use a DOM to read and navigate an XSD in MSXML 3?

Do you have any code samples which show how do this?! (oh, the cheek )

Cheers,

Morris

ADO doesn't have its own XML methods built-in, but it does have the ability to save a recordset into xml format. It can be saved to a file or any object that supports the IStream interface (i.e. XML DOM)

here's a minimal example straight from the MSDN help:
then xDOM contains this XML, which, as I said before, you can transform into whatever format you like using XSLT.

<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
    <s:Schema id="RowsetSchema">
        <s:ElementType name="row" content="eltOnly" rs:CommandTimeout="30">
            <s:AttributeType name="customerid" rs:number="1" rs:writeunknown="true">
                <s:datatype dt:type="string" dt:maxLength="5" rs:fixedlength="true" rs:maybenull="false"/>
            </s:AttributeType>
            <s:AttributeType name="companyname" rs:number="2" rs:writeunknown="true">
                <s:datatype dt:type="string" dt:maxLength="40" rs:maybenull="false"/>
            </s:AttributeType>
            <s:AttributeType name="contactname" rs:number="3" rs:nullable="true" rs:writeunknown="true">
                <s:datatype dt:type="string" dt:maxLength="30"/>
            </s:AttributeType>
            <s:extends type="rs:rowbase"/>
        </s:ElementType>
    </s:Schema>
    <rs:data>
        <z:row customerid="ALFKI" companyname="Alfreds Futterkiste" contactname="Maria Anders"/>
        <z:row customerid="ANATR" companyname="Ana Trujillo Emparedados y helados" contactname="Ana Trujillo"/>

    </rs:data>
</xml>

hth
Phil

Hi folks,

Sorry for the late reply, but firstly I want to say thank for the help, your sugestions have worked. Well, more or less.

Because of the way the xml is created it produces rows like these:

<z:row CarVidNo="33445566" CarRegNo="345rtgy" CarMake="Renault" CarModel="Clio" CarColour="Blue" CarEngine="1.2" CarPrice="5000" />

Now, when I am trying to apply an XSLT template to this with the line:

<xsl:template match="z:row">

The system falls over with the error:

"reference to undeclared namespace prefix" even though the namespace z: is declared at the top of the file:

<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">

No matter how i try to change this, declaring the 'z' character using ascii like so:

<xsl:template match="#122;#58;row">

it still falls over. Anyone got any ideas?

Also, I should prob apologise for putting this here as technically it is an XSLT\XML query. But, then it is in follow up to my orginal query... hummmm, decisions decisions!

Thanks again for all your help,

M.

you have to add the namespace declarations in your xsl too, put them on the xsl:stylesheet element
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:z="#RowsetSchema">

Hi,

Firstly: Thanks for your help Phil - much appreciated!! :o)

Secondly, in the interests of completeness, here is the complete code:

VB Code:


Private Sub Command1_Click()
'Dim conn As New ADODB.Connection

'conn.ConnectionString = "Provider=Microsoft.Jet.OLE.3.51; Data Source=C:\Documents and Settings\User\My Documents\code\Visual Basic\VB 6 and XML"

Dim xDom As New MSXML2.DOMDocument
Dim xXSLT As New MSXML2.DOMDocument
Dim xOutput As New MSXML2.DOMDocument

Dim strXML As String

Dim rsXML As New ADODB.Recordset
Dim sSQL As String, sConn As String

sSQL = "SELECT * From Cars"

sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Cars.mdb"

rsXML.Open sSQL, sConn

'Save Recordset directly into a DOM tree.
rsXML.Save xDom, adPersistXML

'Destroy Recordset
rsXML.Close
Set rsXML = Nothing

'load xslt
xXSLT.async = False

'Save RS_XML
xDom.Save ("C:\Cars.xml")

'Load XSLT file
xXSLT.Load "C:\remove.xslt"

'transform RS_XML
strXML = xDom.transformNode(xXSLT)

'load trnsformed XML
xOutput.loadXML (strXML)

'Save transformed XML
xOutput.Save ("C:\Cars_transformed.xml")

'Confirm Process is complete
MsgBox ("XML extracted and Turned into a file")

End Sub



Here is the XSLT which I used to transform the ADODB.Recordset XML:


<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">


    <xsl:import href="copy.xslt"/>

    <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes" />


      <xsl:template match="*[z:row]">

          <Cars>
                <xsl:apply-templates/>
          </Cars>
      </xsl:template>


        <xsl:template match="s:Schema">
                <xsl:for-each select="z:row">
                    <xsl:apply-templates />
                </xsl:for-each>
        </xsl:template>


        <xsl:template match="z:row/@*">
          <xsl:element name="{name( )}">
                <xsl:value-of select="." />
          </xsl:element>
        </xsl:template>


     <xsl:template match="z:row">
            <xsl:element name="Cars">
                <xsl:apply-templates select="@*"/>
            </xsl:element>
     </xsl:template>
</xsl:stylesheet>


And here is the copy.xslt file:


<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:template match="node( ) | @*">
  <xsl:copy>
    <xsl:apply-templates select="@* | node( )"/>
  </xsl:copy>
</xsl:template>

</xsl:stylesheet>


Hope this helps someone,

Morris

Hi Morris, glad you got it all working now.

Can I just point out that you can combine these two steps into one:
'transform RS_XML
strXML = xDom.transformNode(xXSLT)

'load trnsformed XML
xOutput.loadXML (strXML)

can be combined into one:
xOutput = xDom.transformNodeToObject(xXSLT)

apart from one less line of code there's a subtle advantage of doing it like this - the encoding is preserved when you stream the xml directly between objects (or out to disk using the save method). Whereas if you convert to string variables then you always get UTF-16. You don't have to stream it to a DOM object either, anything that supports the IStream interface will do, for example ADO Stream object can be a good choice if you just want to save the XML to disk, because then you avoid the overhead of the DOM creating an in-memory tree of your XML. But anyway, you won't notice the difference unless your XML gets very large.

While I'm on the subject of language subtleties, there's another great one at work in your code too, with lines like this:
xOutput.loadXML (strXML)
according to the VB docs you shouldn't put parenthesis around the arguments to a Sub unless you prefix it with the Call keyword. But you don't get a syntax error here because what actually happens is that VB interprets the () as a request to pass the argument ByVal instead of the default ByRef. Just to be clear, all of these are OK:
rsXML.Save xDom, adPersistXML ' args passed ByRef
rsXML.Save (xDom), (adPersistXML) ' args passed ByVal
xOutput.loadXML (strXML) ' args passed ByVal
xOutput.loadXML strXML ' args passed ByRef

but this is not OK, you'll get a syntax error:
rsXML.Save(xDom, adPersistXML)

anyways, I think that's more than enough for now :D

rgds
Phil

Hi Phil,

Looks like I am in the presence of greatness!! :D I salute you!!

So, to be clear, the syntax error is caused by the way the VB6 interprates the passing of vars (byref or byval?) to a sub??

Or have I misunderstood this!?

Thanks

Morris