troubleshooting Question

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

Avatar of Saxitalis
Saxitalis asked on
Microsoft SQL ServerXMLC#
2 Comments1 Solution280 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 2 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros