Loading Document Declarion (1st line) of XML file into a data table using Bulk Load??

Posted on 2007-07-23
Last Modified: 2010-04-15
This question pertains to XML and SQL bulk load


I am using Bulk Load to process an XML document into a SQL table. This works fine to get all the elements into the table except the document declaration (?xml version="1.0" encoding="utf-8" standalone="yes"). I must trim this off manually before the sproc below will run.

Question?? Is it possible to load the declaration statement ?xml version="1.0" encoding="utf-8" standalone="yes" into the data table as well intyo a varchar or nvarchar column??

XML file snippet:
<?xml version="1.0" encoding="utf-8" standalone="yes"?><Head><Num><a>11</a><b>37</b><c>07/01/2007</c></Num></Head>

SPROC processing XML elemants with Bulk Load:

DECLARE @docHandle int, @OID int
EXEC sp_xml_preparedocument @docHandle OUTPUT,  @order
--Processing elements from XML file
INSERT INTO ObsDataTemp (a, b,c)
SELECT a, b,c  
 FROM OpenXml( @docHandle, '/Haul/ObsTowNum', 2)   WITH
  ( a int, b int,c smalldatetime )
EXEC sp_xml_removedocument @docHandle SELECT @OID AS [ReportID]

I only want to load the declaration into the data table so the sproc will run and process the rest of the file with out manual intervention.

Translate:Authored by: Saxitalis
Question by:Saxitalis
    LVL 9

    Accepted Solution

    This question is posted in the C# section, so I assume you're calling this stored procedure from a C# application.  If so, you can strip out the document declaration before you pass the XML string to the stored procedure.  Here's one example:

    XmlDocument doc = new XmlDocument();
    doc.Load("your xml doc here");
    string xmlForSproc = doc.DocumentElement.OuterXml;

    Author Comment

    That is exactly what I am doing and that is the exact solutio!

    Thank you very much!!

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Browsing the questions asked to the Experts of this forum, you will be amazed to see how many times people are headaching about monster regular expressions (regex) to select that specific part of some HTML or XML file they want to extract. The examp…
    Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now