Issue with getting data from XML file by using SSIS

Question

I'm getting the data from XML file on the internet like below:

<?xml version="1.0" encoding="ISO-8859-1"?>
-<exchangerates type="Exhange rates" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" refamt="1" refcur="DKK" author="Bank">
-<dailyrates id="2016-07-22">
<currency rate="505.47" desc="Australian dollars" code="AUD"/>
<currency rate="380.36" desc="Bulgarian lev" code="BGN"/>
<currency rate="205.85" desc="Brazilian real" code="BRL"/>
<currency rate="515.31" desc="Canadian dollars" code="CAD"/>
<currency rate="684.99" desc="Swiss francs" code="CHF"/>
<currency rate="101.21" desc="Chinese yuan renminbi" code="CNY"/>
</dailyrates>
</exchangerates>

XSD file looks like:

<?xml version="1.0"?>
<xs:schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="exchangerates">
    <xs:complexType>
      <xs:sequence>
        <xs:element minOccurs="0" name="dailyrates">
          <xs:complexType>
            <xs:sequence>
              <xs:element minOccurs="0" maxOccurs="unbounded" name="currency">
                <xs:complexType>
                  <xs:attribute name="code" type="xs:string" use="optional" />
                  <xs:attribute name="desc" type="xs:string" use="optional" />
                  <xs:attribute name="rate" type="xs:string" use="optional" />
                </xs:complexType>
              </xs:element>
            </xs:sequence>
            <xs:attribute name="id" type="xs:date" use="optional" />
          </xs:complexType>
        </xs:element>
      </xs:sequence>
      <xs:attribute name="type" type="xs:string" use="optional" />
      <xs:attribute name="author" type="xs:string" use="optional" />
      <xs:attribute name="refcur" type="xs:string" use="optional" />
      <xs:attribute name="refamt" type="xs:unsignedByte" use="optional" />
    </xs:complexType>
  </xs:element>
</xs:schema>

But in SSIS (from XML Source Task) I get the data like below. Last column shows me "1" instead of "date" (2016-07-22 in this case). I need this date to be in every row of the "dailyrates_Id" column. Any ideas how to solve this ?

code    desc    rate    dailyrates_Id 
AUD Australian dollars  505.47  1 
BGN Bulgarian lev   380.36  1 
BRL Brazilian real  205.85  1 
CAD Canadian dollars    515.31  1

Metadata of XML Source Task:

Answer 1

Those attributes are on different levels so this is kind of the way the XML Source works. You have a couple of options:

1. Use Sort options with a Merge Join to bring the two sections back together, something like this:

2. Capture the XML in a variable, pass it to a stored proc as a parameter and shred it in the proc, using something like this:

   DECLARE @xml XML = '<?xml version="1.0" encoding="iso-8859-1"?>
   <exchangerates type="Exhange rates" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" refamt="1" refcur="DKK" author="Bank">
       <dailyrates id="2016-07-22">
           <currency rate="505.47" desc="Australian dollars" code="AUD"/>
           <currency rate="380.36" desc="Bulgarian lev" code="BGN"/>
           <currency rate="205.85" desc="Brazilian real" code="BRL"/>
           <currency rate="515.31" desc="Canadian dollars" code="CAD"/>
           <currency rate="684.99" desc="Swiss francs" code="CHF"/>
           <currency rate="101.21" desc="Chinese yuan renminbi" code="CNY"/>
       </dailyrates>
   </exchangerates>'
   
   
   SELECT 
       dr.c.value('@id', 'DATE') id,
       c.c.value('@rate', 'MONEY') rate,
       c.c.value('@desc', 'VARCHAR(50)') [desc],
       c.c.value('@code', 'VARCHAR(50)') code
   FROM @xml.nodes('exchangerates/dailyrates') dr(c)
       CROSS APPLY dr.c.nodes('currency')  c(c)
   

Good luck!

Answer 2

Thanks wBob! I've figured out similar approach yesterday.