Home > Microsoft, SQL Server, T-SQL > T-SQL – Read XML from a SQL Column Example

T-SQL – Read XML from a SQL Column Example

Below is a post to show you how you can read XML data which is held within a SQL table column using T-SQL. In my example below, the SQL column I had to read from was also not held in a XML data type. Therefore the column holding the XML data had to be converted to XML to take advantage of the XML functions.

Firstly, lets take a look at the column which has the XML data. Below it is shown in a column called VEHI.

Select VEHI Columns without XML

What does this XML look like?

<BasicVehicleType xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 

xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<VehicleSystemId xmlns="http://localhost/Schema/CdiCommonTypes">11438229

</VehicleSystemId>

<YearOfManufacture xmlns="http://localhost/Schema/CdiCommonTypes">2002

</YearOfManufacture>

<Make xmlns="http://localhost/Schema/CdiCommonTypes">TOYOTA</Make>

</BasicVehicleType>

 

When I looked into the data type of this column I noticed that it is not XML but a varchar data type. Therefore I recommend converting this to the XML data type first to take use of the XML functions.

 

VEHI Data Type Info

To convert the VEHI column to XML, we simply can use the CAST keyword. The T-SQL would look something like the following:

SELECT TOP 1 CAST(VEHI AS XML) AS VEHI 
FROM LTSAQueries
WHERE VEHI IS NOT NULL
 
After the conversion was done to XML I could then use the T-SQL below to output the Make data from the XML using a select statement. The T-SQL code will look similar to the following. 

T-SQL for reading the XML data

Hope this helps you when playing around with XML SQL data.  If you have better options for dealing with XML than I have outlined here, then feel free to post a comment and let me know.

Greg Olsen

YellowDuckGuy

Advertisements
Categories: Microsoft, SQL Server, T-SQL
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: