Generating
XML from an SQL Database - 3 ways
Suppose
you need to generate an XML file of, let's say, email addresses to pump into an
XSLT transform to send out an email blast
there
are 3 ways to accomplish this task
the
easiest is to use FOR XML in the SQL
an
example (as command text or in a stored procedure)
DECLARE @StartAT INT
SET @StartAt=1
SELECT TOP 100 Empl.* FROM
(SELECT RegistrationID AS
RegistrationKey,
FullName,
Title,
EmailAddress
FROM Registration
WHERE ISNULL(CancelFlag,0)<>1
AND
RegistrationID>=@StartAt) Empl
ORDER BY
RegistrationID
FOR XML RAW ('Registration'), ROOT('Root'), ELEMENTS
this
generates the following
<Root>
<Registration>
<RegistrationKey>108</RegistrationKey>
<FullName>Jane Doe</FullName>
<Title>Grunt</Title>
<EmailAddress>emailaddress@domain.com</EmailAddress>
</Registration>
<Registration>
<RegistrationKey>113</RegistrationKey>
<FullName>Elaine Goode</FullName>
<Title>IT</Title>
<EmailAddress>emailaddress@domain.com</EmailAddress>
</Registration>
</Root>
using
ELEMENTS is the easiest to use and transform (and matches the format of the
third option)
the
downside is of this method that in SQL SERVER 2005 there is a limit of 2K bytes
of XML data that can be returned this way-which isn't a lot for an email blast
going out to hundreds, if not thousands of addressees
in
code, declare an XMLDocument
this
is the VB.NET command to populate the XML document - xmlAttendees is an
instance of XmlDocument and cmd is an SQL command
xmlAttendees.LoadXml(cmd.ExecuteScalar)
a
sesond way is to Fill a Dataset using a TableAdapter, then convert the
resulting dataset to an XMLDocument
pretty
standard ADO.NET - to come up with the same data as the technique above
remove
the last line of the stored procedure or SQL
DECLARE @StartAt INT
SET @StartAt=1
SELECT TOP 100 Empl.* FROM
(SELECT RegistrationID AS
RegistrationKey,
FullName,
Title,
EmailAddress
FROM Registration
WHERE ISNULL(CancelFlag,0)<>1
AND
RegistrationID>=@StartAt) Empl
ORDER BY
RegistrationID
to
insure the node names are the same as from the first technique -
Dim ds AS New
Dataset("Root") is the command in VB.NET to insure the root name will
be Root
to
name the ChildNodes declare a table mapping
Dim
dMap As DataTableMapping
dMap =
da.TableMappings.Add("Table", "Registration")
where da is the DataAdapter that populates a Dataset with the Fill
method
once the Dataset is retrieved from the database use the following
method to convert it to an XML document
xmlAttendees
= ConvertDatasettoXML(ds)
here's the method (in VB.NET)
Private Function
ConvertDatasettoXML(ds As DataSet) As XmlDocument
Dim xmlAttendees As XmlDocument = Nothing
Dim strWtr As StringWriter = Nothing
Dim xmlTxtWtr As XmlTextWriter = Nothing
strWtr = New StringWriter()
xmlTxtWtr = New XmlTextWriter(strWtr)
ds.WriteXml(xmlTxtWtr, XmlWriteMode.IgnoreSchema)
xmlAttendees
= New XmlDocument
xmlAttendees.LoadXml(strWtr.ToString)
Return xmlAttendees
End Function
the third method retrieves a Dataset using the same objects and
tweaks as the second method
converting it to an XML document is done by writing it to disk,
then loading the XML file just created into an XML Document
ds.WriteXML(“SomeFile.xml”)
Dim xmlAttendees As XmlDocument = New XmlDocument
xmlAttendees.Load(“SomeFile.xml”)
Marianne Seggerman
Bruner Associates
