Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Bulk Load - Nested Elements Expand / Collapse
Author
Message
Posted Wednesday, March 31, 2010 10:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 14, 2014 3:07 PM
Points: 9, Visits: 195
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]
Post #893971
Posted Wednesday, March 31, 2010 12:49 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:41 PM
Points: 6,842, Visits: 13,369
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...




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #894092
Posted Wednesday, March 31, 2010 12:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 14, 2014 3:07 PM
Points: 9, Visits: 195
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
Post #894111
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse