Issue with getting data from XML file by using SSIS I'm getting the data from XML file on the internet like below:

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


XSD file looks like:

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


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:
Those attributes are on different levels so this is kind of the way the XML Source works.  You have a couple of options:


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





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


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!