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

This question pertains to XML and SQL bulk load

Hello,

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
BEGIN TRANSACTION
--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 )
  IF @@ERROR<>0 BEGIN ROLLBACK TRANSACTION RETURN -101 END
COMMIT TRANSACTION
EXEC sp_xml_removedocument @docHandle SELECT @OID AS [ReportID]
GO

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.

Thanks,
Sax
Translate:Authored by: Saxitalis
SaxitalisAsked:
Who is Participating?
 
BurntSkyConnect With a Mentor Commented:
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;
0
 
SaxitalisAuthor Commented:
That is exactly what I am doing and that is the exact solutio!

Thank you very much!!
0
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.