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

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
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