March 31, 2010 at 10:39 am
Hi Folks -
I've spent a considerable amount of time between this forum, other web references and my most recent project - only to find an utter lack of 'real world' examples of loading nested XML documents.
I have an XML Ship Status document and I'm trying to load just the header portion in hopes of gaining some insight into what I'm doing incorrectly. I'm using SQLXMLBULKLOAD via VB script in an attempt to add the data to a SQL 2K5 table. When I run the VBS, nothing happens! I have been thrown a few errors regarding element definitions that I've corrected within the XSD. Other than that I end up with an empty table.
VBS, table creation script, xml fragment and xsd follow. I'd REALLY appreciate some help with this mess as I find myself wasting a considerable amount of time on a seemingly simple task!
VBS:
Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.4.0")
objBL.ConnectionString = "Provider=sqloledb;Data Source=Pales;Initial Catalog=Test_XML;Integrated Security=SSPI;"
objBL.SchemaGen = True
objBL.SGDropTables = True
objBL.Execute "FUBAR2.xsd", "SS1.xml"
Set objBL = Nothing
Table:
USE [Test_XML]
GO
/****** Object: Table [dbo].[SSRR_Header] Script Date: 03/31/2010 12:14:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SSRR_Header](
[HDR_ThisDocumentIdentifier] [nvarchar](9) NULL,
[HDR_ThisDocumentDateTime] [datetime] NULL,
[HDR_FromPartnerName] [nvarchar](50) NULL,
[HDR_FromPartnerIdentifier] [nvarchar](13) NULL,
[HDR_FromPartnerAgency] [nvarchar](10) NULL,
[HDR_ToPartnerName] [nvarchar](50) NULL,
[HDR_ToPartnerIdentifier] [nvarchar](9) NULL,
[HDR_ToPartnerAgency] [nvarchar](10) NULL
) ON [PRIMARY]
XML:
[?xml version="1.0" encoding="UTF-8"?]
[ShipmentStatus xmlns:ns0="urn:cidx:names:specification:ces:schema:all:5:0" Version="5.0"]
[Header]
[ThisDocumentIdentifier]
[DocumentIdentifier]7031081[/DocumentIdentifier]
[/ThisDocumentIdentifier]
[ThisDocumentDateTime]
[DateTime DateTimeQualifier="ON"]2010-03-19T12:14:16Z[/DateTime]
[/ThisDocumentDateTime]
[From]
[PartnerInformation]
[PartnerName]My Trading Partner[/PartnerName]
[PartnerIdentifier Agency="GLN"]1234567890000[/PartnerIdentifier]
[/PartnerInformation]
[/From]
[To]
[PartnerInformation]
[PartnerName]My Company Name[/PartnerName]
[PartnerIdentifier Agency="GLN"]9876543210000[/PartnerIdentifier]
[/PartnerInformation]
[/To]
[/Header]
[/ShipmentStatus]
XSD:
[?xml version="1.0" encoding="UTF-8" standalone="no"?]
[xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"]
[xsd:element name="ShipmentStatus" sql:is-constant="1"/][!--ROOT--]
[!--SSRR_Header Table--]
[xsd:element name="Header" sql:relation="SSRR_Header"]
[xsd:complexType]
[xsd:sequence]
[xsd:element name="ThisDocumentIdentifier" sql:mapped="0"]
[xsd:complexType]
[xsd:sequence]
[xsd:element name="DocumentIdentifier" sql:field="HDR_ThisDocumentIdentifier" type="xsd:string"/]
[/xsd:sequence]
[/xsd:complexType]
[/xsd:element]
[xsd:element name="ThisDocumentDateTime" sql:mapped="0"]
[xsd:complexType]
[xsd:sequence]
[xsd:element name="DateTime" sql:field="HDR_ThisDocumentDateTime" type="xsd:string"/]
[/xsd:sequence]
[/xsd:complexType]
[/xsd:element]
[/xsd:sequence]
[/xsd:complexType]
[/xsd:element]
[xsd:element name="From" sql:mapped="0"]
[xsd:complexType]
[xsd:sequence]
[xsd:element name="FromPartnerName" sql:mapped="0"]
[xsd:complexType]
[xsd:sequence]
[xsd:element name="PartnerInformation" sql:mapped="0"]
[xsd:complexType]
[xsd:sequence]
[xsd:element name="PartnerName" sql:field="HDR_FromPartnerName" type="xsd:string"/]
[/xsd:sequence]
[/xsd:complexType]
[/xsd:element]
[/xsd:sequence]
[/xsd:complexType]
[/xsd:element]
[xsd:element name="FromPartnerIdentifier" sql:mapped="0"]
[xsd:complexType]
[xsd:sequence]
[xsd:element name="PartnerInformation" sql:mapped="0"]
[xsd:complexType]
[xsd:sequence]
[xsd:element name="PartnerIdentifier" sql:field="HDR_FromPartnerIdentifier" type="xsd:string"/]
[xsd:element name="Agency" sql:field="HDR_FromPartnerAgency" type="xsd:string"/]
[/xsd:sequence]
[/xsd:complexType]
[/xsd:element]
[/xsd:sequence]
[/xsd:complexType]
[/xsd:element]
[/xsd:sequence]
[/xsd:complexType]
[/xsd:element]
[xsd:element name="To" sql:mapped="0"]
[xsd:complexType]
[xsd:sequence]
[xsd:element name="ToPartnerName" sql:mapped="0"]
[xsd:complexType]
[xsd:sequence]
[xsd:element name="PartnerInformation" sql:mapped="0"]
[xsd:complexType]
[xsd:sequence]
[xsd:element name="PartnerName" sql:field="HDR_ToPartnerName" type="xsd:string"/]
[/xsd:sequence]
[/xsd:complexType]
[/xsd:element]
[/xsd:sequence]
[/xsd:complexType]
[/xsd:element]
[xsd:element name="ToPartnerIdentifier" sql:mapped="0"]
[xsd:complexType]
[xsd:sequence]
[xsd:element name="PartnerInformation" sql:mapped="0"]
[xsd:complexType]
[xsd:sequence]
[xsd:element name="PartnerIdentifier" sql:field="HDR_ToPartnerIdentifier" type="xsd:string"/]
[xsd:element name="Agency" sql:field="HDR_ToPartnerAgency" type="xsd:string"/]
[/xsd:sequence]
[/xsd:complexType]
[/xsd:element]
[/xsd:sequence]
[/xsd:complexType]
[/xsd:element]
[/xsd:sequence]
[/xsd:complexType]
[/xsd:element]
[/xsd:schema]
March 31, 2010 at 12:49 pm
Did you try to find a solution in BOL (BooksOnLine, the SQL Server help system usually installed together with SQL Server)?
You might find a solution in section "XML Bulk Load [SQLXML], examples".
Another reason might be that you're using a XML document with namespace declaration in it (xmlns:ns0). You could try to remove that definition for testing purpose to see if that's the root cause for the empty table.
A totally different alternative could be calling a stored procedure with the xml data provided as a parameter and deal with the XML shredding using XQuery. But I'm not sure if that would cause a performance drop...
March 31, 2010 at 12:59 pm
Hi Lutz -
Probably wouldn't hurt to check BOL. Honestly, I haven't yet - the examples I've found in the past are often too simplified. This is just a wee bit more involved(as you likely know).
I'll pull the namespace declaration out and give it a go - I certainly won't be any worse off!
Thanks For Your Response! I really appreciate you taking the time to get back to me.
Bob
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply