openxml example - without sprocs

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

The only problem is that in SQLCe/SQL Mobile I cannot utilize sprocs

Why don't you post you code including your Xml document so that we can help further?

You can handle it at the front end itself using SqlCeDataAdapter.

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"))
                try
                {
                   
                    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);
                            cmdAdder.ExecuteNonQuery();


                            DataSet ds2 = new DataSet();
                            da.Fill(ds2);
                            ds.Merge(ds2, false, MissingSchemaAction.Add);
                            da.Update(ds.Tables["Table"]);

                        }
                    }
                }
                catch (SqlCeException ex)
                {
                    MessageBox.Show("Could not refresh HandheldInventory Data: " +
                      ex.Message + ")!", "RIO Data Load Error", MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation,
                      MessageBoxDefaultButton.Button1);
                }
            else
                MessageBox.Show("The HandheldInventory.xml is missing, please re-run the desktop synchronization application",
                    "RIO Data Merger Error");
        }

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"

again, I cannot use sprocs - exec sp_xml_preparedocument - sql mobile does not support