1

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: enter image description here

  • ok, I think I've found out what is going on. There are two data outputs from XML Task (currency and dailyrates). Id with date is in dailyrates output. I'll test it tomorrow. – Tomasz Wieczorkowski Jul 25 '16 at 15:14
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:

Sample XML Source package

  1. 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!

1

Thanks wBob! I've figured out similar approach yesterday. enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.