Geocode Locations Using Google Maps v3 API and SSIS

I wanted to do a cool experiment on SSIS that I haven’t tried before. I am really interested in this whole mapping component on SSRS, and I know that most likely the biggest challenge of companies is how to geocode addresses that are already in their databases. So what I wanted to do was to try and retrieve lat/long information from public geocoding web services within SSIS.

Please note that the exercise below is purely for experimentation. Please read the restrictions of each of the geocoding services, and go with whichever is appropriate for your purpose.

Also this is a simplistic code to show you how to get the latitude/longitude information. On your own versions, test thoroughly and add the appropriate exception handlers.

Ok, let’s start.

Step 1 – Look for the appropriate geocoding service.

There are few publicly available ones. Some are limited in content, some are very restricted. Check out the summary here – http://en.wikipedia.org/wiki/Geocoding

For this experiment I am going to use Google Maps v3 API since it’s publicly available, and V3 doesn’t need the Maps API key anymore! Please make sure you read the restrictions of using the Google Maps v3 API before deciding to use it in your applications/systems.

Note that for v3, there are only 2 available output formats: json and xml. Previous version of the API supported other output formats like kml and csv. I am going to stick with XML. (I’ll leave JSON for the next experiment).

Here is an example URL and result: http://maps.googleapis.com/maps/api/geocode/xml?address=Pacific+West+Outlet,Gilroy,California,US&sensor=false

Try to study the structure, it will help later on with the parsing for latitude and longitude.

Step 2 – Prep your package

Create your SSIS package. Drop a data flow task, and add your source. In my case my source is an OLE DB source pointing to my SQL Server table that has address fields already.


Read the rest of this entry »

VN:F [1.9.22_1171]
Rating: 8.9/10 (15 votes cast)
VN:F [1.9.22_1171]
Rating: +4 (from 4 votes)

SQL Server XML Red Gate Ebook, XQuery Labs

Jacob Sebastian is a SQL Server XML Guru!

Check out his collection for XQUery Labs. This is the first 12 of his series, and right now he has 43 and counting:

XQuery Sample Scripts

* XQuery Lab 1 – Transforming rows to columns
* XQuery Lab 2 – An example using OUTER APPLY
* XQuery Lab 3 – Filtering specific nodes
* XQuery Lab 4 – Joining XML Nodes with a Relational Table
* XQuery Lab 5 – Working with Namespaces
* XQuery Lab 6 – Processing Header-Detail information
* XQuery Lab 7 – Extracting a comma separated list of values
* XQuery Lab 8 – How to update the attribute value of an XML variable?
* XQuery Lab 9 – How to delete an attribute from an XML variable?
* XQuery Lab 10 – How to insert an attribute to an XML variable
* XQuery Lab 11 – How to insert an element to an XML variable
* XQuery Lab 12 – Different ways of reading values from an XML variable

Jacob Sebastian has also released a free ebook via RedGate – The Art of XSD – SQL Server XML Schema Collections

Check it out, all 483 pages! :)

VN:F [1.9.22_1171]
Rating: 8.7/10 (3 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

SQLXML : How to Work With XML Elements (or Nodes) in SQL Server

Assume this is your XML snippet

DECLARE @authorsXML XML
SET @authorsXML = '
<Author>
  <ID>172-32-1176</ID>
  <LastName>White</LastName>
  <FirstName>Johnson</FirstName>
  <Address>
    <Street>10932 Bigge Rd.</Street>
    <City>Menlo Park</City>
    <State>CA</State>
  </Address>
</Author>
'

Note that the examples below show how you can manipulate XML nodes – but most operations require singleton values. Ie, the changes must affect one and only one node. Thus in most the examples we specify the index of the node we want to target.

For example:

(/Author/LastName)[1]

which means we are only targetting the first instance of LastName under the Author node. If you need to do a mass update, you may need to use a cursor.

Read the rest of this entry »

VN:F [1.9.22_1171]
Rating: 9.5/10 (46 votes cast)
VN:F [1.9.22_1171]
Rating: +11 (from 11 votes)

SQLXML : How to List Columns That Have the XML Data Type

   1: -- this lists the table catalog, table name, 
   2: -- column name, and data type
   3: SELECT 
   4:     TABLE_CATALOG,
   5:     TABLE_NAME,
   6:     COLUMN_NAME, 
   7:     DATA_TYPE 
   8: FROM 
   9:     INFORMATION_SCHEMA.COLUMNS
  10: WHERE 
  11:     DATA_TYPE = 'xml'
  12:  

 

   1: -- this lists the corresponding schemas
   2: SELECT 
   3:     DISTINCT
   4:     OBJECT_NAME(sys.columns.object_id)        AS 'TableName',
   5:     sys.columns.name                    AS 'ColName',
   6:     sys.xml_schema_collections.name            AS 'Schema' 
   7: FROM 
   8:     sys.columns
   9:     LEFT JOIN     sys.xml_schema_collections 
  10:     ON sys.columns.xml_collection_id = sys.xml_schema_collections.xml_collection_id
  11: ORDER BY 
  12:     OBJECT_NAME(sys.columns.object_id), sys.columns.name    
  13:  

VN:F [1.9.22_1171]
Rating: 9.0/10 (1 vote cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

SQLXML : How to List Schema Elements and Attributes

   1: SELECT
   2:     sys.xml_schema_collections.xml_collection_id    AS CollectionID,
   3:     sys.xml_schema_collections.name                 AS SchemaName,
   4:     sys.xml_schema_elements.name                    AS ElementName,
   5:     sys.xml_schema_attributes.name                  AS AttributeName
   6: FROM
   7:     sys.xml_schema_collections
   8:     INNER JOIN sys.xml_schema_attributes
   9:     ON sys.xml_schema_collections.xml_collection_id =  sys.xml_schema_attributes.xml_collection_id
  10:     INNER JOIN sys.xml_schema_elements
  11:     ON sys.xml_schema_collections.xml_collection_id = sys.xml_schema_elements.xml_collection_id
  12: WHERE
  13:     sys.xml_schema_collections.name NOT LIKE 'sys'

Sample Result:

SQLXML Sample Result - Elements and Attribute Names

VN:F [1.9.22_1171]
Rating: 8.0/10 (5 votes cast)
VN:F [1.9.22_1171]
Rating: +1 (from 1 vote)

SQLXML : How to Create an XML Schema

The following is an example of how to create an XML Schema in SQL Server.

   1: -- DROP IF EXISTS
   2: IF    EXISTS (SELECT 1 
   3:               FROM  sys.xml_schema_collections 
   4:               WHERE name='SampleSchema')
   5:    DROP XML SCHEMA COLLECTION SampleSchema
   6:  
   7: CREATE XML SCHEMA COLLECTION SampleSchema AS 
   8: '<?xml version="1.0" encoding="utf-8"?>
   9: <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  10:   <xsd:element name="Sample">
  11:     <xsd:complexType>
  12:       <xsd:attribute name="SampleID" type="xsd:integer" />
  13:       <xsd:attribute name="Name" type="xsd:string" />
  14:       <xsd:attribute name="Description" type="xsd:string" />
  15:     </xsd:complexType>
  16:   </xsd:element>
  17: </xsd:schema>'

 

To check your XML Schema:

Method 1: Go to your database > Programmability > Types > XML Schema Collections

SQLXML - XML Schema Collection

Method 2: You can use the xml_schema_namespace function to query the schema from within SSMS

   1:  
   2: SELECT
   3:    xml_schema_namespace(N'dbo',N'SampleSchema') 

 

Later on I will post additional sample schemas which use different SQL XML data types.

VN:F [1.9.22_1171]
Rating: 8.9/10 (7 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)
   1: SELECT * 
   2: FROM  sys.xml_schema_collections 
   3: WHERE name='NameOfSchema'

VN:F [1.9.22_1171]
Rating: 10.0/10 (2 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)
`