InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 10:40:16 GMT20http://www.sqlservercentral.com/Forums/Topic893971-21-1.aspxHi 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.BobWed, 31 Mar 2010 12:59:03 GMTBobW-496277http://www.sqlservercentral.com/Forums/Topic893971-21-1.aspxDid 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...Wed, 31 Mar 2010 12:49:11 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic893971-21-1.aspxHi 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![b]VBS:[/b]Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.4.0")objBL.ConnectionString = "Provider=sqloledb;Data Source=Pales;Initial Catalog=Test_XML;Integrated Security=SSPI;"objBL.SchemaGen = TrueobjBL.SGDropTables = TrueobjBL.Execute "FUBAR2.xsd", "SS1.xml"Set objBL = Nothing[b]Table:[/b]USE [Test_XML]GO/****** Object: Table [dbo].[SSRR_Header] Script Date: 03/31/2010 12:14:15 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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][b]XML:[/b][?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][b]XSD:[/b][?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]Wed, 31 Mar 2010 10:39:11 GMTBobW-496277