by I have a question on the implementation of XML Templates in SQL*Server 2000
which I hope someone can help me with. Forgive me if this is a silly
question, but I'm only starting to get to grips with XML and SQL*Server.
I want to receive an XML file (from Biztalk, but that should not matter)
that contains the following structure:
<docroot>
<tracking>
<tracking elements.>
</tracking>
<bespokedetails>
<detail elements.>
</bespokedetails>
<data>
<data elements.>
</data>
</docroot>
This document is destined to be inserted into a database table that has one
field for each element in <tracking> and <bespokedetails> (the child
elements are fixed) and a blob/large varchar field that will contain the
contents of <data> in raw XML format (the child elements for data are not
fixed).
I have downloaded and played with the example from MSDN that uses a template
file, a stored procedure, and the OPENXML function to take an XML document
and insert the XML data into relational fields, one per attribute. I think
this is the way to go, but can't quite get my head around how I would change
this example to use elements rather than attributes. Ideally, I would like
the template to be quite simple and have the work performed by a DB stored
procedure. I guess that it would be this procedure that would need to use
the OPENXML or something to get specific elements from the XML document
which can then be passed to the insert t-sql.
Any ideas or thoughts on this?
Thanks in advance,
Steve