I am trying to take the national weather service XML snaphot and import into a SQL 2005 DB
http://www.weather.gov/data/current_obs/KHOT.xml
in theory this looks like it should work, but I get no data every time it runs
here is a sample of myXML source
<?xml version="1.0" encoding="ISO-8859-1"?>
<current_observation version="1.0"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="http://www.weather.gov/data/current_obs/current_observation.xsd">
<credit>NOAA's National Weather Service</credit>
<credit_URL>http://weather.gov/</credit_URL>
<image>
<url>http://weather.gov/images/xml_logo.gif</url>
<title>NOAA's National Weather Service</title>
<link>http://weather.gov</link>
</image>
<suggested_pickup>15 minutes after the hour</suggested_pickup>
<suggested_pickup_period>60</suggested_pickup_period>
<location>Hot Springs, Memorial Field Airport, AR</location>
<station_id>KHOT</station_id>
<latitude>34.480</latitude>
<longitude>-93.100</longitude>
<observation_time>Last Updated on Sep 4, 12:53 pm CDT</observation_time>
<observation_time_rfc822>Tue, 4 Sep 2007 12:53:00 -0500 CDT</observation_time_rfc822>
<weather>Overcast</weather>
<temperature_string>81 F (27 C)</temperature_string>
<temp_f>81</temp_f>
<temp_c>27</temp_c>
<relative_humidity>74</relative_humidity>
<wind_string>From the South at 5 MPH</wind_string>
<wind_dir>South</wind_dir>
<wind_degrees>180</wind_degrees>
<wind_mph>4.6</wind_mph>
<wind_gust_mph>NA</wind_gust_mph>
<pressure_string>30.04" (1016.6 mb)</pressure_string>
<pressure_mb>1016.6</pressure_mb>
<pressure_in>30.04</pressure_in>
<dewpoint_string>72 F (22 C)</dewpoint_string>
<dewpoint_f>72</dewpoint_f>
<dewpoint_c>22</dewpoint_c>
<heat_index_string>85 F (29 C)</heat_index_string>
<heat_index_f>85</heat_index_f>
<heat_index_c>29</heat_index_c>
<windchill_string>NA</windchill_string>
<windchill_f>NA</windchill_f>
<windchill_c>NA</windchill_c>
<visibility_mi>10.00</visibility_mi>
<icon_url_base>http://weather.gov/weather/images/fcicons/</icon_url_base>
<icon_url_name>ovc.jpg</icon_url_name>
<two_day_history_url>http://www.weather.gov/data/obhistory/KHOT.html</two_day_history_url>
<ob_url>http://www.nws.noaa.gov/data/METAR/KHOT.1.txt</ob_url>
<disclaimer_url>http://weather.gov/disclaimer.html</disclaimer_url>
<copyright_url>http://weather.gov/disclaimer.html</copyright_url>
<privacy_policy_url>http://weather.gov/notice.html</privacy_policy_url>
</current_observation>
The auto generated XSD file only gives me the subtable and no data
byt using XSD.exe I generated this file and it gives me the right structure. gives me two outputs from the xml source I need the one called current_observations)
but when I run this I get no data. When I look at the previews I get no data. I has to be something goofy in the XSD file but I dont know what it is.
<?xml version="1.0" encoding="utf-8"?>
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="current_observation">
<xs:complexType>
<xs:sequence>
<xs:element name="credit" type="xs:string" minOccurs="0" msdata:Ordinal="0" />
<xs:element name="credit_URL" type="xs:string" minOccurs="0" msdata:Ordinal="1" />
<xs:element name="suggested_pickup" type="xs:string" minOccurs="0" msdata:Ordinal="3" />
<xs:element name="suggested_pickup_period" type="xs:string" minOccurs="0" msdata:Ordinal="4" />
<xs:element name="location" type="xs:string" minOccurs="0" msdata:Ordinal="5" />
<xs:element name="station_id" type="xs:string" minOccurs="0" msdata:Ordinal="6" />
<xs:element name="latitude" type="xs:string" minOccurs="0" msdata:Ordinal="7" />
<xs:element name="longitude" type="xs:string" minOccurs="0" msdata:Ordinal="8" />
<xs:element name="observation_time" type="xs:string" minOccurs="0" msdata:Ordinal="9" />
<xs:element name="observation_time_rfc822" type="xs:string" minOccurs="0" msdata:Ordinal="10" />
<xs:element name="weather" type="xs:string" minOccurs="0" msdata:Ordinal="11" />
<xs:element name="temperature_string" type="xs:string" minOccurs="0" msdata:Ordinal="12" />
<xs:element name="temp_f" type="xs:string" minOccurs="0" msdata:Ordinal="13" />
<xs:element name="temp_c" type="xs:string" minOccurs="0" msdata:Ordinal="14" />
<xs:element name="relative_humidity" type="xs:string" minOccurs="0" msdata:Ordinal="15" />
<xs:element name="wind_string" type="xs:string" minOccurs="0" msdata:Ordinal="16" />
<xs:element name="wind_dir" type="xs:string" minOccurs="0" msdata:Ordinal="17" />
<xs:element name="wind_degrees" type="xs:string" minOccurs="0" msdata:Ordinal="18" />
<xs:element name="wind_mph" type="xs:string" minOccurs="0" msdata:Ordinal="19" />
<xs:element name="wind_gust_mph" type="xs:string" minOccurs="0" msdata:Ordinal="20" />
<xs:element name="pressure_string" type="xs:string" minOccurs="0" msdata:Ordinal="21" />
<xs:element name="pressure_mb" type="xs:string" minOccurs="0" msdata:Ordinal="22" />
<xs:element name="pressure_in" type="xs:string" minOccurs="0" msdata:Ordinal="23" />
<xs:element name="dewpoint_string" type="xs:string" minOccurs="0" msdata:Ordinal="24" />
<xs:element name="dewpoint_f" type="xs:string" minOccurs="0" msdata:Ordinal="25" />
<xs:element name="dewpoint_c" type="xs:string" minOccurs="0" msdata:Ordinal="26" />
<xs:element name="heat_index_string" type="xs:string" minOccurs="0" msdata:Ordinal="27" />
<xs:element name="heat_index_f" type="xs:string" minOccurs="0" msdata:Ordinal="28" />
<xs:element name="heat_index_c" type="xs:string" minOccurs="0" msdata:Ordinal="29" />
<xs:element name="windchill_string" type="xs:string" minOccurs="0" msdata:Ordinal="30" />
<xs:element name="windchill_f" type="xs:string" minOccurs="0" msdata:Ordinal="31" />
<xs:element name="windchill_c" type="xs:string" minOccurs="0" msdata:Ordinal="32" />
<xs:element name="visibility_mi" type="xs:string" minOccurs="0" msdata:Ordinal="33" />
<xs:element name="icon_url_base" type="xs:string" minOccurs="0" msdata:Ordinal="34" />
<xs:element name="icon_url_name" type="xs:string" minOccurs="0" msdata:Ordinal="35" />
<xs:element name="two_day_history_url" type="xs:string" minOccurs="0" msdata:Ordinal="36" />
<xs:element name="ob_url" type="xs:string" minOccurs="0" msdata:Ordinal="37" />
<xs:element name="disclaimer_url" type="xs:string" minOccurs="0" msdata:Ordinal="38" />
<xs:element name="copyright_url" type="xs:string" minOccurs="0" msdata:Ordinal="39" />
<xs:element name="privacy_policy_url" type="xs:string" minOccurs="0" msdata:Ordinal="40" />
<xs:element name="image" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="url" type="xs:string" minOccurs="0" />
<xs:element name="title" type="xs:string" minOccurs="0" />
<xs:element name="link" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="version" type="xs:string" />
</xs:complexType>
</xs:element>
<xs:element name="NewDataSet" msdata:IsDataSet="true">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element ref="current_observation" />
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
I have a feeling im going to feel retarded once i find the answer...
can anyone tell me what ive screwed up?
|||I guess I feel better knowing that no one else who has seen this over the last few days has any idea either.|||Have you tried using a source script component?|||no, i dont know what is wrong with it and therefore i dont know what I would script
i just looked and didnt see a script input under data flow.
this is what i am using in SSIS
http://www.vetteprojects.com/cthulhu/misc/work/ssis.pdf
|||anyone ?|||The published xsd file from the NWS for this file is
<?xml version="1.0"?>
<xsd
chema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:complexType name="imageType">
<xsdequence>
<xsd:element name="url" type="xsd:anyURI" minOccurs="0"/>
<xsd:element name="title" type="xsd:token" minOccurs="0"/>
<xsd:element name="link" type="xsd:anyURI" minOccurs="0"/>
</xsdequence>
</xsd:complexType>
<xsd:element name="current_observation">
<xsd:complexType>
<xsdequence>
<xsd:element name="credit" type="xsd:token" minOccurs="0"/>
<xsd:element name="credit_URL" type="xsd:anyURI" minOccurs="0"/>
<xsd:element name="image" type="imageType" minOccurs="0"/>
<xsd:element name="suggested_pickup" type="xsd:token" minOccurs="0"/>
<xsd:element name="suggested_pickup_period" type="xsd:integer" minOccurs="0"/>
<xsd:element name="location" type="xsd:token" minOccurs="0"/>
<xsd:element name="station_id" type="xsd:token" minOccurs="0"/>
<xsd:element name="latitude" type="xsdtring" minOccurs="0"/>
<xsd:element name="longitude" type="xsdtring" minOccurs="0"/>
<xsd:element name="elevation" type="xsd:integer" minOccurs="0"/>
<xsd:element name="observation_time" type="xsd:token" minOccurs="0"/>
<xsd:element name="observation_time_rfc822" type="xsd:token" minOccurs="0"/>
<xsd:element name="weather" type="xsd:token" minOccurs="0"/>
<xsd:element name="temperature_string" type="xsd:token" minOccurs="0"/>
<xsd:element name="temp_f" type="xsd:integer" minOccurs="0"/>
<xsd:element name="temp_c" type="xsd:integer" minOccurs="0"/>
<xsd:element name="relative_humidity" type="xsd:integer" minOccurs="0"/>
<xsd:element name="wind_string" type="xsd:token" minOccurs="0"/>
<xsd:element name="wind_dir" type="xsdtring" minOccurs="0"/>
<xsd:element name="wind_degrees" type="xsd:integer" minOccurs="0"/>
<xsd:element name="wind_mph" type="xsd
ecimal" minOccurs="0"/>
<xsd:element name="wind_gust_mph" type="xsd:integer" minOccurs="0"/>
<xsd:element name="pressure_string" type="xsd:token" minOccurs="0"/>
<xsd:element name="pressure_mb" type="xsdecimal" minOccurs="0"/>
<xsd:element name="pressure_in" type="xsdecimal" minOccurs="0"/>
<xsd:element name="dewpoint_string" type="xsd:token" minOccurs="0"/>
<xsd:element name="dewpoint_f" type="xsd:integer" minOccurs="0"/>
<xsd:element name="dewpoint_c" type="xsd:integer" minOccurs="0"/>
<xsd:element name="heat_index_string" type="xsd:token" minOccurs="0"/>
<xsd:element name="heat_index_f" type="xsd:integer" minOccurs="0"/>
<xsd:element name="heat_index_c" type="xsd:integer" minOccurs="0"/>
<xsd:element name="windchill_string" type="xsd:token" minOccurs="0"/>
<xsd:element name="windchill_f" type="xsd:integer" minOccurs="0"/>
<xsd:element name="windchill_c" type="xsd:integer" minOccurs="0"/>
<xsd:element name="visibility_mi" type="xsdecimal" minOccurs="0"/>
<xsd:element name="icon_url_base" type="xsd:anyURI" minOccurs="0"/>
<xsd:element name="icon_name" type="xsd:anyURI" minOccurs="0"/>
<xsd:element name="two_day_history_url" type="xsd:anyURI" minOccurs="0"/>
<xsd:element name="icon_url_name" type="xsd:anyURI" minOccurs="0"/>
<xsd:element name="ob_url" type="xsd:anyURI" minOccurs="0"/>
<xsd:element name="disclaimer_url" type="xsd:anyURI" minOccurs="0"/>
<xsd:element name="copyright_url" type="xsd:anyURI" minOccurs="0"/>
<xsd:element name="privacy_policy_url" type="xsd:anyURI" minOccurs="0"/>
</xsdequence>
<xsd:attribute name="version" type="xsdtring" default="1.0"/>
</xsd:complexType>
</xsd:element>
</xsdchema>
is also fails to give me any output columns for "current_observation" and only give me the output for image_type
and when I just leave it with the image table and try to dump this into a file it is empty as well.
so im almost thiking the generated schmea that I am using above is not the problem.
does XML have to be validated before it can be imported into SSIS?
|||I read here that the XML Source cannot handle XML where the root node has attributes or elements of simple types. To consume this XML, you'll have to change it to have a bogus root element. I just did this with your schema:
Code Snippet
<xsd:element name="bogus_root">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="current_observation">
<xsd:complexType>
<xsd:sequence>
...
This worked and produced two outputs: current_observation and image. The XML Source added a current_observation_id column to both outputs to associate the two.|||I made this change..
and added the appropriate closing parameters
and it does indeed now recognize the current observation side of it.. but it still isnt pulling any data.
when I goto preview the data it still shows no records
When I run the package it writes 0 rows
Code Snippet
<?xml version="1.0"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:complexType'>http://www.w3.org/2001/XMLSchema">http://www.w3.org/2001/XMLSchema">
<xsd:complexType name="imageType">
<xsd:sequence>
<xsd:element name="url" type="xsd:anyURI" minOccurs="0"/>
<xsd:element name="title" type="xsd:token" minOccurs="0"/>
<xsd:element name="link" type="xsd:anyURI" minOccurs="0"/>
</xsd:sequence>
</xsd:complexType>
<xsd:element name="bogus_root">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="current_observation">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="credit" type="xsd:token" minOccurs="0"/>
<xsd:element name="credit_URL" type="xsd:anyURI" minOccurs="0"/>
<xsd:element name="image" type="imageType" minOccurs="0"/>
<xsd:element name="suggested_pickup" type="xsd:token" minOccurs="0"/>
<xsd:element name="suggested_pickup_period" type="xsd:integer" minOccurs="0"/>
<xsd:element name="location" type="xsd:token" minOccurs="0"/>
<xsd:element name="station_id" type="xsd:token" minOccurs="0"/>
<xsd:element name="latitude" type="xsd:string" minOccurs="0"/>
<xsd:element name="longitude" type="xsd:string" minOccurs="0"/>
<xsd:element name="elevation" type="xsd:integer" minOccurs="0"/>
<xsd:element name="observation_time" type="xsd:token" minOccurs="0"/>
<xsd:element name="observation_time_rfc822" type="xsd:token" minOccurs="0"/>
<xsd:element name="weather" type="xsd:token" minOccurs="0"/>
<xsd:element name="temperature_string" type="xsd:token" minOccurs="0"/>
<xsd:element name="temp_f" type="xsd:integer" minOccurs="0"/>
<xsd:element name="temp_c" type="xsd:integer" minOccurs="0"/>
<xsd:element name="relative_humidity" type="xsd:integer" minOccurs="0"/>
<xsd:element name="wind_string" type="xsd:token" minOccurs="0"/>
<xsd:element name="wind_dir" type="xsd:string" minOccurs="0"/>
<xsd:element name="wind_degrees" type="xsd:integer" minOccurs="0"/>
<xsd:element name="wind_mph" type="xsd:decimal" minOccurs="0"/>
<xsd:element name="wind_gust_mph" type="xsd:integer" minOccurs="0"/>
<xsd:element name="pressure_string" type="xsd:token" minOccurs="0"/>
<xsd:element name="pressure_mb" type="xsd:decimal" minOccurs="0"/>
<xsd:element name="pressure_in" type="xsd:decimal" minOccurs="0"/>
<xsd:element name="dewpoint_string" type="xsd:token" minOccurs="0"/>
<xsd:element name="dewpoint_f" type="xsd:integer" minOccurs="0"/>
<xsd:element name="dewpoint_c" type="xsd:integer" minOccurs="0"/>
<xsd:element name="heat_index_string" type="xsd:token" minOccurs="0"/>
<xsd:element name="heat_index_f" type="xsd:integer" minOccurs="0"/>
<xsd:element name="heat_index_c" type="xsd:integer" minOccurs="0"/>
<xsd:element name="windchill_string" type="xsd:token" minOccurs="0"/>
<xsd:element name="windchill_f" type="xsd:integer" minOccurs="0"/>
<xsd:element name="windchill_c" type="xsd:integer" minOccurs="0"/>
<xsd:element name="visibility_mi" type="xsd:decimal" minOccurs="0"/>
<xsd:element name="icon_url_base" type="xsd:anyURI" minOccurs="0"/>
<xsd:element name="icon_name" type="xsd:anyURI" minOccurs="0"/>
<xsd:element name="two_day_history_url" type="xsd:anyURI" minOccurs="0"/>
<xsd:element name="icon_url_name" type="xsd:anyURI" minOccurs="0"/>
<xsd:element name="ob_url" type="xsd:anyURI" minOccurs="0"/>
<xsd:element name="disclaimer_url" type="xsd:anyURI" minOccurs="0"/>
<xsd:element name="copyright_url" type="xsd:anyURI" minOccurs="0"/>
<xsd:element name="privacy_policy_url" type="xsd:anyURI" minOccurs="0"/>
</xsd:sequence>
<xsd:attribute name="version" type="xsd:string" default="1.0"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
I was afraid you were going to say that..
I am trying to grab this file every hour and import it. making a change to the xml everytime is not practical.
did you see the first XSD I posted? it is at the same stage right now as the provided xsd after your mods can it be modified to allow import without xml file mod ?
i will try this in the morning and let you know
thanks
|||No, you will have to change the XML to match the schema change I made.
This would be simple to do in an XSL Transform, called in an XML Task before you process the data with the data flow.
|||I tried to mod the xml file and still didnt get a record out of the file.
maybe I modded the file wrong
Code Snippet
<?xml version="1.0" encoding="ISO-8859-1"?>
<bogus_root>
<current_observation version="1.0"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance
.
.
.
<copyright_url>http://weather.gov/disclaimer.html</copyright_url>
<privacy_policy_url>http://weather.gov/notice.html</privacy_policy_url>
</current_observation>
</bogus_root>
No comments:
Post a Comment