SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Bulk Load - Nested Elements


SQL Bulk Load - Nested Elements

Author
Message
BobW-496277
BobW-496277
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 196
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]
LutzM
LutzM
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23953 Visits: 13559
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
BobW-496277
BobW-496277
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 196
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search