How Do I import XML

Question

How Do I import XML

Charlottecb

Hall of Fame

Points: 3932
More actions
March 31, 2005 at 7:52 am

#163091

Hi all.
SQLserver 2000
I have never worked with XML so please forgive me if I am asking a stupid question.
I have been asked to import the contents of an Orenance Survey XML file into columns on a table in my DB.
I have created the table with a column for each node in the XML file.
How do I proceed from here ?
Thanks CCB

Here is a bit of the XML file: -
<?xml version="1.0" encoding="UTF-8" ?>
- <osgb:FeatureCollection xmlns:osgb="http://www.ordnancesurvey.co.uk/xml/namespaces/osgb" xmlns:gml="http://www.opengis.net/gml" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.ordnancesurvey.co.uk/xml/namespaces/osgb http://www.ordnancesurvey.co.uk/xml/schema/v2/OSDNFFeatures.xsd" fid="GDS-2185-1">
<gml:description>Ordnance Survey, (c) Crown Copyright. All rights reserved, 2003-03-29</gml:description>
- <gml:boundedBy>
<gml:null>unknown</gml:null>
</gml:boundedBy>
<osgb:queryTime>2003-03-29T16:37:45</osgb:queryTime>
- <osgb:queryExtent>
- <osgb:Rectangle srsName="osgb:BNG">
<gml:coordinates>278000.000,186000.000 280000.000,188000.000</gml:coordinates>
</osgb:Rectangle>
</osgb:queryExtent>
- <osgb:addressPointMember>
- <osgb:AddressPoint fid="osgb1000002124226633">
<osgb:version>1</osgb:version>
<osgb:versionDate>2002-08-18</osgb:versionDate>
<osgb:theme>Address</osgb:theme>
- <osgb:addressStatus>
<osgb:matchStatus>Matched</osgb:matchStatus>
<osgb:physicalStatus>Existing</osgb:physicalStatus>
<osgb:positionalQuality accuracy="Surveyed">Final</osgb:positionalQuality>
<osgb:structureType>Permanent Building</osgb:structureType>
</osgb:addressStatus>
<osgbSAPR>APPXAR8J5GG4ABX0NV</osgbSAPR>
- <osgb:point>
- <gmloint srsName="osgb:BNG">
<gml:coordinates>278218.800,187803.800</gml:coordinates>
</gmloint>
</osgb:point>
- <osgb:postalAddress>
<osgb:buildingNumber>200</osgb:buildingNumber>
<osgb:thoroughfare>MARGAM ROAD</osgb:thoroughfare>
<osgb:postTown>PORT TALBOT</osgb:postTown>
<osgb:postCode type="Small">SA13 2BU</osgb:postCode>
<osgb:deliveryPointSuffix>1N</osgb:deliveryPointSuffix>
</osgb:postalAddress>
<osgb:postalAddressDate>2002-07-19</osgb:postalAddressDate>
<osgb:referenceToTopographicArea xlink:href="#osgb1000022051714" />
</osgb:AddressPoint>
</osgb:addressPointMember>
- <osgb:addressPointMember>
- <osgb:AddressPoint fid="osgb1000002124226634">
<osgb:version>1</osgb:version>
<osgb:versionDate>2002-08-18</osgb:versionDate>
<osgb:theme>Address</osgb:theme>
- <osgb:addressStatus>
<osgb:matchStatus>Matched</osgb:matchStatus>
<osgb:physicalStatus>Existing</osgb:physicalStatus>
<osgb:positionalQuality accuracy="Surveyed">Final</osgb:positionalQuality>
<osgb:structureType>Permanent Building</osgb:structureType>
</osgb:addressStatus>
<osgbSAPR>APRTAR8J5GG4ABXGNV</osgbSAPR>
- <osgb:point>
- <gmloint srsName="osgb:BNG">
<gml:coordinates>278219.600,187797.300</gml:coordinates>
</gmloint>
</osgb:point>
- <osgb:postalAddress>
<osgb:buildingNumber>202</osgb:buildingNumber>
<osgb:thoroughfare>MARGAM ROAD</osgb:thoroughfare>
<osgb:postTown>PORT TALBOT</osgb:postTown>
<osgb:postCode type="Small">SA13 2BU</osgb:postCode>
<osgb:deliveryPointSuffix>1P</osgb:deliveryPointSuffix>
</osgb:postalAddress>
<osgb:postalAddressDate>2002-07-19</osgb:postalAddressDate>
<osgb:referenceToTopographicArea xlink:href="#osgb1000022051715" />
</osgb:AddressPoint>
</osgb:addressPointMember>
- <osgb:addressPointMember>
- <osgb:AddressPoint fid="osgb1000002124226978">
<osgb:version>1</osgb:version>
<osgb:versionDate>2002-08-18</osgb:versionDate>
<osgb:theme>Address</osgb:theme>
- <osgb:addressStatus>
<osgb:matchStatus>Matched</osgb:matchStatus>
<osgb:physicalStatus>Existing</osgb:physicalStatus>
<osgb:positionalQuality accuracy="Surveyed">Final</osgb:positionalQuality>
<osgb:structureType>Permanent Building</osgb:structureType>
</osgb:addressStatus>
<osgbSAPR>APK2AR825GR4AEXGQV</osgbSAPR>
- <osgb:point>
- <gmloint srsName="osgb:BNG">
<gml:coordinates>278105.200,187940.900</gml:coordinates>
</gmloint>
</osgb:point>
- <osgb:postalAddress>
<osgb:buildingNumber>4</osgb:buildingNumber>
<osgb:thoroughfare>GREENFIELD AVENUE</osgb:thoroughfare>
<osgb:postTown>PORT TALBOT</osgb:postTown>
<osgb:postCode type="Small">SA13 2LW</osgb:postCode>
<osgb:deliveryPointSuffix>1H</osgb:deliveryPointSuffix>
</osgb:postalAddress>
<osgb:postalAddressDate>2002-07-19</osgb:postalAddressDate>
<osgb:referenceToTopographicArea xlink:href="#osgb1000022051839" />
</osgb:AddressPoint>
</osgb:addressPointMember>
- <osgb:boundedBy>
- <gml:Box srsName="osgb:BNG">
<gml:coordinates>278000.000,186000.000 280000.000,188000.000</gml:coordinates>
</gml:Box>
</osgb:boundedBy>
</osgb:FeatureCollection>

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Michael Schumacher Valued Member Points: 65 More actions · Answer 1

I have used the SQLXMLBulkload.3.0 add-in for SQL Server. Using a ActiveX component in a DTS package I run the follwing code:

Function Main()set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.3.0")

objBL.ConnectionString = "provider=SQLOLEDB;data source=localhost;database=XYZ;integrated security=SSPI"

objBL.ErrorLogFile = "c:\path\xyzerror.log"

objBL.Execute "C:\path\Schema.xsd", "C:\path\XYZ\file.xml"

set objBL=Nothing

Main = DTSTaskExecResult_Success

End Function

You can download the SQLXMLBulkload.3.0 from Microsoft

You need to create the schema file, but it looks as though there is predefined schema (OSDNFFeatures.xsd) And then in the schema file you will need to identify the (sql:relation="") for the table(s) to load into.

Good luck