• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

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.
0
tbaseflug
Asked:
tbaseflug
1 Solution
 
mrichmonCommented:
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
0
 
tbaseflugAuthor Commented:
The only problem is that in SQLCe/SQL Mobile I cannot utilize sprocs
0
 
Anthony PerkinsCommented:
Why don't you post you code including your Xml document so that we can help further?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Aneesh RetnakaranDatabase AdministratorCommented:
You can handle it at the front end itself using SqlCeDataAdapter.
0
 
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"))
                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");
        }
0
 
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"
0
 
tbaseflugAuthor Commented:
again, I cannot use sprocs - exec sp_xml_preparedocument - sql mobile does not support
0
 
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
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now