Converse to BulkLoad...

[Daniel Bass]

I've an Annotated Schema file (XSD) which describes how data is laid out in
multiple tables in SQL Server.

I've a .Net application that creates an instance of the BulkLoad COM object,
and calls the Execute method, populating my tables using the XSD with my XML
data.

Question is, how do I use the same XSD to then read that data from the
database?

Thanks for your time.
Daniel.

 

[Daniel Bass]

Thanks for your prompt reply.

The situation I have is that I'm using a generic "loader" which I pass XML
into, and using some XPath configuration, decide which XSD fits the XML and
loads the data into my tables. The Xml contains multiple levels and so I'll
be pushing data into parent/child/grandchild structured tables.

In my code, I also want some way of initiating a data "pull", so that on
some event, given some XSD, I'll pull the data from the database. I need the
XSD because in the compiled application I'll have no way of knowing the
table structure...

As I discuss this, I'm wondering about creating a "select" stored procedure
for each message type, which uses FOR XML to correctly format my text into
the XML I want. Then all I need is some event table which tells the compiled
application what new message I need to retrieve, which then lets me know the
sp I need to call...

What do you think?

Thanks,
Dan.

Daniel,

Well, you don't really need the XSD to read the data from the database.
You can just read the data from the field when you perform a select. You
can call the ExecuteXmlReader on the SqlCommand to get the data (make sure
that you use a FOR XML clause, or that you select one column, one row that
returns text/ntext/xml data).

Because you used an XSD to validate the data going in, you can assume
that the field only contains data which conforms to that XSD. You don't
have to worry about validating on the way out.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I've an Annotated Schema file (XSD) which describes how data is laid out
in multiple tables in SQL Server.

I've a .Net application that creates an instance of the BulkLoad COM
object, and calls the Execute method, populating my tables using the XSD
with my XML data.

Question is, how do I use the same XSD to then read that data from the
database?

Thanks for your time.
Daniel.