HELP! configuring schema file...

[kcrewjap]

I'm trying to load an xml document into a sql server table using vbscript and sqlxmlbulkload. The xml file has a header element with the filename, date, record counts, etc, followed by transaction elements. I'd like to merge some of the header info with the transaction data but I can't figure out how to configure the schema file to do this with a single target table insert.

Excerpt of data:

CT.xml:
<?xml version="1.0"?>
<infocdr>
<file_name>ct.xml</file_name>
<ticket>
<unique_key>29820088</unique_key>
<content_code>1234</content_code>
</ticket>
<ticket>
<unique_key>29820089</unique_key>
<content_code>5678</content_code>
</ticket>
</infocdr>


target table ddl:
CREATE TABLE [dbo].[Tickets](
[file_name] [varchar](50) NULL,
[unique_key] [varchar](10) NULL,
[content_code] [varchar](4) NULL
) ON [PRIMARY]


desired records inserted in table:
ct.xml 29820088 1234
ct.xml 29820089 5678


CT.vbs:
Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString = "provider=SQLOLEDB.1;data
source=localhost;database=northwind;integrated security=SSPI"
objBL.ErrorLogFile = "c:\temp\error1.log"
objBL.Execute "c:\CT_schema.xml", "c:\CT.xml"
Set objBL = Nothing


CT_schema.xml (so far):
<xsd:schema xmlns:xsd="

http://www.w3.org/2001/XMLSchema"

xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="file_name" type="xsd:string" sql:relation="Tickets"/>
<xsd:element name="ticket" sql:relation="Tickets">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="unique_key" type="xsd:string" />
<xsd:element name="content_code" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>

but this inserts undesired results:
ct.xml NULL NULL
NULL 29820088 1234
NULL 29820089 5678

 

[tsuji]

Not without a twist. That kind of one file_name and multiple ticket suit the paradigm of parent-child relationship. In that perspective, you can do it like this.

[1] Create two tables

[1.1] Create a table, say FileName, as the "parent".
[tt]
CREATE TABLE [dbo].[FileName] (
[file_name] [varchar](50) NULL,
) ON [PRIMARY]
[/tt]
[1.2] Create the table you have been having.
[tt]
CREATE TABLE [dbo].[Tickets](
[file_name] [varchar](50) NULL,
[unique_key] [varchar](10) NULL,
[content_code] [varchar](4) NULL
) ON [PRIMARY]
[/tt]
[2] Create the annotated schema like this.
[tt]
<xsd:schema xmlns:xsd="[ignore]

http://www.w3.org/2001/XMLSchema

[/ignore]"
xmlns:sql="[ignore]urn:schemas-microsoft-com:mapping-schema[/ignore]">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="parentchild"
parent="FileName"
parent-key="file_name"
child="Tickets"
child-key="file_name"
/>
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="infocdr" sql:relation="FileName">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="file_name" type="xsd:string" />
<xsd:element name="ticket" sql:relation="Tickets" sql:relationship="parentchild">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="unique_key" type="xsd:string" />
<xsd:element name="content_code" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
[/tt]
[2.1] Take note that the parent table sql:relation is established on the container node (infocdr) a level up as appeared in your xsd content of the root xsd:schema.

[3] After bulkload, if you really "hate" to have another table around, delete that FileName table. It's up to you.

 

[kcrewjap]

This still doesnt combine the two tables into one..


desired records inserted in table:
ct.xml 29820088 1234
ct.xml 29820089 5678

not
ct.xml NULL NULL
NULL 29820088 1234
NULL 29820089 5678

any help is much appreciated.

Thanks

 

[tsuji]

Still doesn't? Impossible, on the info used. You have to do the work more seriously.

 

[kcrewjap]

You are right, it worked perfectly. thanks you for all of your help.