openxml example - without sprocs

Can anyone give me an openxml example of inserting into a SQL table, from an xml file - the caveat is that I am using SQLMobile, so cannot utilize SPROCS at all.
Who is Participating?
Anthony PerkinsCommented:
If you cannot even call extended stored procedures such as sp_xml_preparedocument, than as far as OPENXML is concerned you are SOL
Well here is an example, but I have ever only done in a stored procedure or in a script.  I hope that can work

DECLARE @DailyComments varchar(8000)
SET @DailyComments = '<root><DailyComment CommentDate="5/4/2005" Comment="Bob left 2 hours early" PublicCommentFlag="0" /><DailyComment CommentDate="5/16/2005" Comment="Vacation - approved" PublicCommentFlag="1" /></root>'

DECLARE @idoc int

-- Create an internal representation of the DailyComments XML document
EXEC sp_xml_preparedocument @idoc OUTPUT, @DailyComments

-- Select out the values we need and insert as records into tblDailyComments
INSERT INTO tblDailyComments
SELECT CommentDate, Comment, PublicCommentFlag
FROM OPENXML(@idoc, '/root/DailyComment',1)
WITH (CommentDate DateTime, Comment varchar(200), PublicCommentFlag bit)

-- Cleanup the XML doc
EXEC sp_xml_removedocument @idoc
tbaseflugAuthor Commented:
The only problem is that in SQLCe/SQL Mobile I cannot utilize sprocs
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Anthony PerkinsCommented:
Why don't you post you code including your Xml document so that we can help further?
Aneesh RetnakaranDatabase AdministratorCommented:
You can handle it at the front end itself using SqlCeDataAdapter.
tbaseflugAuthor Commented:
Here is the code that I am currently using - however, the dataadapter.update mthod is slow - in that it takes 1+ mins to load 1000 rows:

        private void Refresh_HandheldInventory()
            if (File.Exists(appPath + @"\data\HandheldInventory.xml"))
                    DataSet ds = new DataSet();
                    ds.ReadXml(appPath + @"\data\HandheldInventory.xml");

                    if (ds.Tables.Count > 0)
                        using (SqlCeConnection cn = GetConnection())
                            SqlCeDataAdapter da;

                            da = new SqlCeDataAdapter("SELECT * FROM MobileInventory", cn);

                            SqlCeCommandBuilder cb;
                            cb = new SqlCeCommandBuilder(da);
                            da.InsertCommand = cb.GetInsertCommand();
                            da.InsertCommand.Connection = cn;

                            String sSQLCommand = "DELETE FROM MobileInventory;";
                            SqlCeCommand cmdAdder = new SqlCeCommand(sSQLCommand, cn);

                            DataSet ds2 = new DataSet();
                            ds.Merge(ds2, false, MissingSchemaAction.Add);

                catch (SqlCeException ex)
                    MessageBox.Show("Could not refresh HandheldInventory Data: " +
                      ex.Message + ")!", "RIO Data Load Error", MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation,
                MessageBox.Show("The HandheldInventory.xml is missing, please re-run the desktop synchronization application",
                    "RIO Data Merger Error");
Anthony PerkinsCommented:
My C# is a little rusty, but the basic idea is that you load the Xml into a string and than execute it like any query with ExecuteNonQuery, using a long string like this:

"DECLARE @idoc int;  exec sp_xml_preparedocument @idoc OUTPUT, " + YourXmlStringGoesHere + "; INSERT YourTable (Col1, Col2, ...)SELECT Col1, Col2, ... FROM OPENXML(@idoc, '<YourXPathGoesHere>',' 2) WITH (...); exec sp_xml_removedocument @idoc"
tbaseflugAuthor Commented:
again, I cannot use sprocs - exec sp_xml_preparedocument - sql mobile does not support
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.