SQLXML Importing Attribute and Elements into same DB record

J

JimP

I'm importing XML files into a SQLExpress DB using SQLXML and VB.net
2005. I have a simple *.xsd schema file that is pulling in all
attributes from the <Activity> element.

It also has a <Header> element that I'd like to add the 'Run'
attribute for each record in the TA_HeaderID field. So, in the
example, '879" would be added to each record in the TA_HeaderID field.

Schema file...
<?xml version="1.0" encoding="utf-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Activity" sql:relation="TerminalTable">
<xsd:complexType>
<xsd:attribute name="Group" sql:field="TA_Group"
type="xsd:string" use="required" />
<xsd:attribute name="GN" sql:field="TA_GN"
type="xsd:unsignedByte" use="required" />
<xsd:attribute name="Set" sql:field="TA_Set" type="xsd:string"
use="required" />
<xsd:attribute name="Term" sql:field="TA_Term" type="xsd:string"
use="required" />
<xsd:attribute name="TN" sql:field="TA_TN"
type="xsd:unsignedShort" use="required" />
</xsd:complexType>
</xsd:element>
</xsd:schema>

..XML file...
<?xml version="1.0" ?>
<TA>
<Header Version="V418.05"Run="879"/>
<Activity Group="DAB" GN="2" Set="W01" Term="XCTPAY" TN="512"/>
<Activity Group="DAB" GN="2" Set="W01" Term="XGDB03" TN="523"/>
<Activity Group="DAB" GN="2" Set="W01" Term="XGDB06" TN="526"/>
<Activity Group="DAB" GN="2" Set="W01" Term="XGDB09" TN="1080"/>
</TA>

CREATE TABLE [dbo].[TA](
[TA_HeaderID] [int] ,
[TA_Group] [varchar](3),
[TA_GN] [smallint] ,
[TA_Set] [varchar](3) ,
[TA_Term] [varchar](6) ,
[TA_TN] [smallint] ,
) ON [PRIMARY]

I tried adding the following to the schema but all this does is add
one
record to the DB which is not what I desire.

<xsd:element name="Header" sql:relation="TA">
<xsd:complexType>
<xsd:attribute name="Run" sql:field="TA_HeaderId"
type="xsd:string" use="required" />
</xsd:complexType>
</xsd:element>

If I add the line
<xsd:attribute name="Run" sql:field="TA_HeaderId" type="xsd:string"
use="required" />
under
'<xsd:element name="Activity" sql:relation="TerminalTable">'
....TA_HeaderId remains empty

How do you edit the schema file to include this Attribute field for
each record?
thanks
Jim
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,968
Messages
2,570,153
Members
46,699
Latest member
AnneRosen

Latest Threads

Top