Insert Attribute value into multiple records using SQLXML

[Jimdeb03]

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.

Schema file...
<?xml version="1.0" encoding="utf-8"?>
<xsd:schema xmlns:xsd="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema"[/URL] xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
  <xsd:element name="Activity" sql:relation="TTable">
    <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].[TTable](
	[TA_HeaderID] [int] ,
	[TA_Group] [varchar](3),
	[TA_GN] [smallint] ,
	[TA_Set] [varchar](3) ,
	[TA_Term] [varchar](6) ,
	[TA_TN] [smallint] ,
) ON [PRIMARY]

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.

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="TTable">
    <xsd:complexType>
      <xsd:attribute name="Run" sql:field="TA_HeaderId" type="xsd:string" use="required" />
    </xsd:complexType>
  </xsd:element>

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

 

[Miros]

I would think your xsd:attribute name="Run" should be part of the Activity element, not part of the Header element.

 

[Jimdeb03]

If I edit the schema file to include the attribute name="Run" under the "Activity" element, TA_id remains empty. In the xml, the "Run" attribute is under the 'Header' element, not 'Activity'.

<?xml version="1.0" encoding="utf-8"?>
<xsd:schema xmlns:xsd="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema"[/URL] xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
  <xsd:element name="Activity" sql:relation="TTable">
    <xsd:complexType>
      [b]xsd:attribute name="Run" sql:field="TA_HeaderId" type="xsd:string" use="required" />[/b]
      <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>

 

[tsuji]

[0] You are not forced to change the xml to load the data. That is an unnecessary restriction on flexibility. Besides, the appearance of Header element with multiple Activity element is very natural. It is in normal form. Repeating same Run attribute to all Activity elements would make the thing worse as it would not be off of a dbase normal form.

[1] Only you've realize that the data is in a one-to-many relationship. You have to make up two tables instead of one. In the parent it contains the Run as primary key. Let's say THeader table. It will be reappeared in the second child table TTable in your shown script as a foreign key.

[2] In the THeader table, there would be only one column namely the Run attribute, TH_HeaderId, say. Since you don't want to map the Version. If you map Version as well, it will be two columns.

[3] This will be the general outline/look of the approach.
[tt]
<xsd:schema xmlns:xsd="[ignore]

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

[/ignore]"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="HeaderActivity"
parent="THeader"
parent-key="TH_HeaderId"
child="TTable"
child-key="TA_TN" />
</xsd:appinfo>
</xsd:annotation>

<xsd:element name="TA" sql:relation="THeader" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Header">
<xsd:complexType>
<xsd:attribute name="Version" type="xsd:string" sql:is-constant="1" use="required" />
<xsd:attribute name="Run" type="xsd:unsignedShort" sql:field="TH_HeaderId" use="required" />
</xsd:complexType>
</xsd:element>
<xsd:element name="Activity" sql:relation="TTable" sql:relationship="HeaderActivity">
<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:sequence>
</xsd:complexType>
</xsd:element>

</xsd:schema>
[/tt]
[3.1] The above is a sketch only. You might have to debug some fine detail.

 

[Jimdeb03]

Thanks for the input.
I too came to the same conclusion to separate the XML data into multiple tables depending on the number of Elements.

You went a step further and put in a relationship between the two tables

My plan is to dump the XML data into temporary tables and use a stored procedure to populate the permanent tables from these temporary tables. This way, I can verify the integrity of the download and add Time/Date stamp for each record.

Is there any way to INSERT a Date Field via SQLXML?

Also, I'll be downloading multiple XML files, all with the similar Header Information per day. Some will have more than one Element (not including the Header), that will populate their respective table(s). So the "Run" attribute will be unique per day and propagated throughout all the tables.

So in essence, this same Key will be used throughout all tables for a given day. But my thinking is to create another Key for each group of related tables that originate from the same XML file.

Is this possible to add via the schema file too?

Thanks
Jim