Hello.,

I am writing an XDR document to import an XML document into SQL Server 2000 using SQLXML 3.0 via a DTS package, and I am stuck on retrieving attribute information from a higher level.

Here is a sample of the XML data I am trying to import.
----------------------------------------------------------------------------------------------------------------
<?xml version="1.0" encoding="ISO-8859-1"?>
<CLAIMATTR>
<MarketClaims recordCount="10" dateStamp = "2009-05-08">
<MarketClaim feedID = "20090508000001" FMRef = "D123451" CALMRef = "E987650" Depot = "0001" SubDepot =
"0001" Registration = "0001" FundCode = "CIS01" TradBrokerCode="56521" AssetCode="1234567" TradeNominal =
"1000" ExDate = "15 May 2009" RecordDate = "20 May 2009" PayDate = "25 May 2009" ClaimTaxRate =
"21.0000000" />
</MarketClaims>
</CLAIMATTR>
----------------------------------------------------------------------------------------------------------------
You will notice that in the MarketClaims element, there are two attributes RecordCount and DateStamp, which are, if you like header information. The data, one element per record is then contained in ther sub-element MarketClaim


My question, is, in the XDR file, how do I import the RecordCount and DateStamp data into the same table as the MarketClaim elements.

Here is the XDR file I am using.
--------------------------------------------------------------------------------------------------------------------
?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql" >

<ElementType name="CLAIMATTR" sql:is-constant="1">
<element type="MarketClaims" />
</ElementType>

<ElementType name="MarketClaims" sql:is-constant="1">
<element type="MarketClaim" />

</ElementType>

<ElementType name ="MarketClaim" sql:relation="CLAIMATTR">
<AttributeType name="feedID" dt:type="Text" />
<AttributeType name="FMRef" dt:type="Text" />
<AttributeType name="CALMRef" dt:type="Text" />
<AttributeType name="Depot" dt:type="Number" />
<AttributeType name="SubDepot" dt:type="Number" />
<AttributeType name="Registration" dt:type="Number" />
<AttributeType name="FundCode" dt:type="Text" />
<AttributeType name="TradBrokerCode" dt:type="Number" />
<AttributeType name="AssetCode" dt:type="Text" />
<AttributeType name="TradeNominal" dt:type="Number" />
<AttributeType name="ExDate" dt:type="Date" />
<AttributeType name="RecordDate" dt:type="Date" />
<AttributeType name="PayDate" dt:type="Date" />
<AttributeType name="ClaimTaxRate" dt:type="Percentage" />

<attribute type ="feedID" sql:field="xml_header_record" />
<attribute type ="FMRef" sql:field="unique_ref" />
<attribute type ="CALMRef" sql:field="calm_ref" />
<attribute type ="Depot" sql:field="depot" />
<attribute type ="SubDepot" sql:field="subdepot" />
<attribute type ="Registration" sql:field="registration" />
<attribute type ="FundCode" sql:field="fund_code" />
<attribute type ="TradBrokerCode" sql:field="trading_broker_code" />
<attribute type ="AssetCode" sql:field="asset_code" />
<attribute type ="TradeNominal" sql:field="nominal" />
<attribute type ="ExDate" sql:field="ex_date" />
<attribute type ="RecordDate" sql:field="record_date" />
<attribute type ="PayDate" sql:field="pay_date" />
<attribute type ="ClaimTaxRate" sql:field="tax_rate" />
</ElementType>
</Schema>
--------------------------------------------------------------------------------------------------------------------

What I cannot work out how to do, is refer to the MarketClaim element and its attributes in the MarketClaims element.

Any ideas would be most gratefully received