July 15, 2003 at 6:56 am
We are going to be receiving XML files and need to be able to load our tables based on the XML files. I'm new to SQL Server and only familiar with XML.
Can someone give me tips on how to do this? An example of the XML is:
<?xml version="1.0" ?>
<SEASONSTATS SPORT="Football" SOURCE="DakStats" VERSION="3.01" DATE="5/14/02002" TMID="98" LEAGUEID="11509">
<TEAMS>
<TEAM TMID="98" TMACTIVE="True" ABBREV="SDSU" LOCATION="Santo Domingo State University" NICKNAME="Jackrabbits" TEAMSHORT="SDSU Jacks" TEAMCODE="SDSU" GENDER="False" LEAGUE="" DIVISION="" CONFERENCE="" COACH="" COLLEGEID="1111" LEAGUEID="11509" />
</TEAMS>
<ROSTERS>
<ROSTER PLRID="939" TMID="98" PLRACTIVE="True" NUMBER="TM" FIRSTNAME="" LASTNAME="TEAM" POSITION="" HOMETOWN="" HEIGHT="" WEIGHT="" YEAR="" TEAMCODE="" PLRCODE="" MISCLINE1="" MISCLINE2="" MISCLINE3="" MISCLINE4="" LEAGUEID="1234567890" TMLEAGUEID="11509" />
<ROSTER PLRID="965" TMID="98" PLRACTIVE="True" NUMBER="1" FIRSTNAME="Carol" LASTNAME="Jung" POSITION="OH" HOMETOWN="" HEIGHT="5-8" WEIGHT="" YEAR="Jr." TEAMCODE="" PLRCODE="" MISCLINE1="" MISCLINE2="" MISCLINE3="" MISCLINE4="" LEAGUEID="1234567890" TMLEAGUEID="11509" />
<ROSTER PLRID="966" TMID="98" PLRACTIVE="True" NUMBER="2" FIRSTNAME="Jennifer" LASTNAME="Sabers" POSITION="OH" HOMETOWN="" HEIGHT="5-9" WEIGHT="" YEAR="Fr." TEAMCODE="" PLRCODE="" MISCLINE1="" MISCLINE2="" MISCLINE3="" MISCLINE4="" LEAGUEID="1234567890" TMLEAGUEID="11509" />
<ROSTER PLRID="967" TMID="98" PLRACTIVE="True" NUMBER="3" FIRSTNAME="Melissa" LASTNAME="Phillips" POSITION="OH" HOMETOWN="" HEIGHT="5-8" WEIGHT="" YEAR="Jr." TEAMCODE="" PLRCODE="" MISCLINE1="" MISCLINE2="" MISCLINE3="" MISCLINE4="" LEAGUEID="1234567890" TMLEAGUEID="11509" />
<ROSTER PLRID="968" TMID="98" PLRACTIVE="True" NUMBER="4" FIRSTNAME="Amber" LASTNAME="Rush" POSITION="M/OH" HOMETOWN="" HEIGHT="5-10" WEIGHT="" YEAR="So." TEAMCODE="" PLRCODE="" MISCLINE1="" MISCLINE2="" MISCLINE3="" MISCLINE4="" LEAGUEID="1234567890" TMLEAGUEID="11509" />
<ROSTER PLRID="969" TMID="98" PLRACTIVE="True" NUMBER="5" FIRSTNAME="Donna" LASTNAME="Sterud" POSITION="OH" HOMETOWN="" HEIGHT="5-9" WEIGHT="" YEAR="Jr." TEAMCODE="" PLRCODE="" MISCLINE1="" MISCLINE2="" MISCLINE3="" MISCLINE4="" LEAGUEID="1234567890" TMLEAGUEID="11509" />
<ROSTER PLRID="970" TMID="98" PLRACTIVE="True" NUMBER="6" FIRSTNAME="Kay" LASTNAME="Patrick" POSITION="OH" HOMETOWN="" HEIGHT="5-11" WEIGHT="" YEAR="Sr." TEAMCODE="" PLRCODE="" MISCLINE1="" MISCLINE2="" MISCLINE3="" MISCLINE4="" LEAGUEID="1234567890" TMLEAGUEID="11509" />
<ROSTER PLRID="971" TMID="98" PLRACTIVE="True" NUMBER="7" FIRSTNAME="Linda" LASTNAME="Karlstad" POSITION="Setter" HOMETOWN="" HEIGHT="5-7" WEIGHT="" YEAR="Sr." TEAMCODE="" PLRCODE="" MISCLINE1="" MISCLINE2="" MISCLINE3="" MISCLINE4="" LEAGUEID="1234567890" TMLEAGUEID="11509" />
<ROSTER PLRID="972" TMID="98" PLRACTIVE="True" NUMBER="8" FIRSTNAME="Jennifer" LASTNAME="Bruinsma" POSITION="Setter" HOMETOWN="" HEIGHT="5-7" WEIGHT="" YEAR="Fr." TEAMCODE="" PLRCODE="" MISCLINE1="" MISCLINE2="" MISCLINE3="" MISCLINE4="" LEAGUEID="1234567890" TMLEAGUEID="11509" />
<ROSTER PLRID="973" TMID="98" PLRACTIVE="True" NUMBER="9" FIRSTNAME="Leann" LASTNAME="Holler" POSITION="MH" HOMETOWN="" HEIGHT="5-10" WEIGHT="" YEAR="Sr." TEAMCODE="" PLRCODE="" MISCLINE1="" MISCLINE2="" MISCLINE3="" MISCLINE4="" LEAGUEID="1234567890" TMLEAGUEID="11509" />
<ROSTER PLRID="974" TMID="98" PLRACTIVE="True" NUMBER="10" FIRSTNAME="Pam" LASTNAME="Judd" POSITION="OH" HOMETOWN="" HEIGHT="5-9" WEIGHT="" YEAR="Fr." TEAMCODE="" PLRCODE="" MISCLINE1="" MISCLINE2="" MISCLINE3="" MISCLINE4="" LEAGUEID="1234567890" TMLEAGUEID="11509" />
<ROSTER PLRID="975" TMID="98" PLRACTIVE="True" NUMBER="11" FIRSTNAME="Robin" LASTNAME="Wicks" POSITION="MH" HOMETOWN="" HEIGHT="5-11" WEIGHT="" YEAR="Fr." TEAMCODE="" PLRCODE="" MISCLINE1="" MISCLINE2="" MISCLINE3="" MISCLINE4="" LEAGUEID="1234567890" TMLEAGUEID="11509" />
<ROSTER PLRID="976" TMID="98" PLRACTIVE="True" NUMBER="12" FIRSTNAME="Laura" LASTNAME="Thorsteinson" POSITION="M/OH" HOMETOWN="" HEIGHT="5-10" WEIGHT="" YEAR="So." TEAMCODE="" PLRCODE="" MISCLINE1="" MISCLINE2="" MISCLINE3="" MISCLINE4="" LEAGUEID="1234567890" TMLEAGUEID="11509" />
<ROSTER PLRID="977" TMID="98" PLRACTIVE="True" NUMBER="14" FIRSTNAME="Marissa" LASTNAME="Iverson" POSITION="MH" HOMETOWN="" HEIGHT="6-0" WEIGHT="" YEAR="So." TEAMCODE="" PLRCODE="" MISCLINE1="" MISCLINE2="" MISCLINE3="" MISCLINE4="" LEAGUEID="1234567890" TMLEAGUEID="11509" />
</ROSTERS>
</SEASONSTATS>
Thanks so much for your help!!
July 15, 2003 at 7:24 am
You can create an internal representation of an XML doc using sp_xml_preparedocument and then query using OPENXML. Search for sp_xml_preparedocument and OPENXML in BOL.
Regards,
Andy Jones
.
July 15, 2003 at 12:17 pm
You can use an updategram (included in the second SQL Server 2000 Web Release) which allow you to provide a schema-compliant XML document to be mapped back to the database.
http://search.microsoft.com/search/results.aspx?View=msdn&st=a&s=2&c=4&qu=updategrams
All the best,
Dale
All the best,
Dale
Author: An Introduction to SQL Server 2005 Management Studio
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply