I have this forecast data in xml. Im having issues with storing it in a table. I guess my first question would be, what is the best way to store this data in a table. Right now I just one table set up to store it all, yes I know its not normalized, but its a start. I was thinking of storing all this data as two tables, one for the forecast day info (first query), and one for each part of each day (second query). Here is what I have so far.
declare @zip varchar(5)
set @zip = '54901'
DECLARE @idoc int
declare @property varchar(8000)
set @property = '
<weather ver="2.0">
<head>
<locale>en_US</locale>
<form>MEDIUM</form>
<ut>F</ut>
<ud>mi</ud>
<us>mph</us>
<up>in</up>
<ur>in</ur>
</head>
<loc id="54901">
<dnam>Oshkosh, WI (54901)</dnam>
<tm>5:42 PM</tm>
<lat>44.02</lat>
<lon>-88.54</lon>
<sunr>7:06 AM</sunr>
<suns>6:14 PM</suns>
<zone>-5</zone>
</loc>
<dayf>
<lsup>10/13/03 5:07 PM CDT</lsup>
<day d="0" t="Monday" dt="Oct 13">
<hi>N/A</hi>
<low>44</low>
<sunr>7:06 AM</sunr>
<suns>6:14 PM</suns>
<part p="d">
<icon>30</icon>
<t>Partly Cloudy</t>
<wind>
<s>11</s>
<gust>N/A</gust>
<d>239</d>
<t>WSW</t>
</wind>
<ppcp>0</ppcp>
<hmid>62</hmid>
</part>
<part p="n">
<icon>11</icon>
<t>Showers</t>
<wind>
<s>6</s>
<gust>N/A</gust>
<d>284</d>
<t>WNW</t>
</wind>
<ppcp>50</ppcp>
<hmid>77</hmid>
</part>
</day>
<day d="1" t="Tuesday" dt="Oct 14">
<hi>55</hi>
<low>36</low>
<sunr>7:07 AM</sunr>
<suns>6:12 PM</suns>
<part p="d">
<icon>11</icon>
<t>Showers</t>
<wind>
<s>14</s>
<gust>N/A</gust>
<d>318</d>
<t>NW</t>
</wind>
<ppcp>50</ppcp>
<hmid>67</hmid>
</part>
<part p="n">
<icon>29</icon>
<t>Partly Cloudy</t>
<wind>
<s>10</s>
<gust>N/A</gust>
<d>298</d>
<t>WNW</t>
</wind>
<ppcp>20</ppcp>
<hmid>79</hmid>
</part>
</day>
<day d="2" t="Wednesday" dt="Oct 15">
<hi>57</hi>
<low>33</low>
<sunr>7:09 AM</sunr>
<suns>6:11 PM</suns>
<part p="d">
<icon>34</icon>
<t>Mostly Sunny</t>
<wind>
<s>14</s>
<gust>N/A</gust>
<d>292</d>
<t>WNW</t>
</wind>
<ppcp>10</ppcp>
<hmid>59</hmid>
</part>
<part p="n">
<icon>29</icon>
<t>Partly Cloudy</t>
<wind>
<s>6</s>
<gust>N/A</gust>
<d>317</d>
<t>NW</t>
</wind>
<ppcp>10</ppcp>
<hmid>80</hmid>
</part>
</day>
<day d="3" t="Thursday" dt="Oct 16">
<hi>54</hi>
<low>32</low>
<sunr>7:10 AM</sunr>
<suns>6:09 PM</suns>
<part p="d">
<icon>30</icon>
<t>Partly Cloudy</t>
<wind>
<s>10</s>
<gust>N/A</gust>
<d>303</d>
<t>WNW</t>
</wind>
<ppcp>10</ppcp>
<hmid>67</hmid>
</part>
<part p="n">
<icon>29</icon>
<t>Partly Cloudy</t>
<wind>
<s>4</s>
<gust>N/A</gust>
<d>260</d>
<t>W</t>
</wind>
<ppcp>10</ppcp>
<hmid>73</hmid>
</part>
</day>
<day d="4" t="Friday" dt="Oct 17">
<hi>55</hi>
<low>38</low>
<sunr>7:11 AM</sunr>
<suns>6:07 PM</suns>
<part p="d">
<icon>30</icon>
<t>Partly Cloudy</t>
<wind>
<s>7</s>
<gust>N/A</gust>
<d>191</d>
<t>S</t>
</wind>
<ppcp>10</ppcp>
<hmid>71</hmid>
</part>
<part p="n">
<icon>29</icon>
<t>Partly Cloudy</t>
<wind>
<s>4</s>
<gust>N/A</gust>
<d>284</d>
<t>WNW</t>
</wind>
<ppcp>10</ppcp>
<hmid>82</hmid>
</part>
</day>
</dayf>
</weather>'
print @property
EXEC sp_xml_preparedocument @idoc OUTPUT, @property
SELECT @zip, *
FROM OPENXML (@idoc, '/weather/dayf/day', 8)
with (
d char(1),
t varchar(10),
dt varchar(10),
hi varchar(5) 'hi',
low varchar(5) 'low',
sunr varchar(10) 'sunr',
suns varchar(10) 'suns'
)
SELECT @zip, *
FROM OPENXML (@idoc, '/weather/dayf/day/part', 8)
with (
d int '../@d',
p char(1) ,
icon int 'icon',
t varchar(20) 't',
winds int 'wind/s',
windgust varchar(5) 'wind/gust',
windd int 'wind/d',
windt varchar(5) 'wind/t',
ppcp int 'ppcp',
hmid int 'hmid'
)
go
Any help is greatly appreciated. Thanks.
Eric Stephani
