xsd sql schema relation problem bulk load - self join
- From: b <b@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 2 Nov 2006 01:44:01 -0800
Here is a snippet of my xml (based on the ecb daily) and with xmlns removed:
<?xml version="1.0" encoding="UTF-8"?>
<gesmes>
<Cube>
<Cube time='2006-11-01'>
<Cube currency='USD' rate='1.2757'/>
<Cube currency='JPY' rate='149.24'/>
<Cube currency='CYP' rate='0.5770'/>
<Cube currency='CZK' rate='28.066'/>
<Cube currency='DKK' rate='7.4547'/>
</Cube>
</Cube>
</gesmes>
Here is my xsd:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema";
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="curr_fixing1"
parent="curr_fixing"
parent-key="px_date"
child="curr_fixing"
child-key="px_date" inverse="true" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="gesmes" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Cube" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Cube" sql:relation="curr_fixing" nillable
="false">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Cube" sql:relation="curr_fixing"
nillable ="false" sql:relationship="curr_fixing1" maxOccurs="unbounded">
<xsd:complexType>
<xsd:attribute name="currency" sql:field="curr_code"
type="xsd:string" use="required"/>
<xsd:attribute name="rate" sql:field="curr_fix"
type="xsd:string" use="required"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="time" sql:field="px_date"
type="xsd:string" use="required"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
Here is my table:
px_date curr_code curr_fix
2006-11-01 USD 1.2757
2006-11-01 JPY 149.24
2006-11-01 CYP 0.577
2006-11-01 CZK 28.066
2006-11-01 DKK 7.4547
2006-11-01 NULL NULL
vbscript then bulk loads and executes xml and schema.
This is perfect apart from the last row with NULLs, I've looked at different
ways of doing this, I'm not sure what I'm doing wrong. I need to specify the
relationship on the last Cube element as the one before is set to relation on
the table. But since both elements map to the same table its creating a
problem. So I created a relationship which self joins, but this seems to be
creating the last join back on the last attribute which is a null. There
must be some setting to stop nulls. I've tried using nillable, but this
doesn't seem to work as it isn't the xml with nill values the join is
creating this. Any ideas?
.
- Prev by Date: Re: Problem with CDATA section in FOR XML EXPLICIT
- Next by Date: RE: Thoughts on storing address data with XML data type?
- Previous by thread: Problem with CDATA section in FOR XML EXPLICIT
- Next by thread: Re: Combining XML Files using SSIS or T-SQL etc.
- Index(es):
Relevant Pages
|
Loading
