Dave's IT Blog: XML What's All That About Anyway?

I recently had to work with XML within SQL 2005. It was a learning experience for sure.

So what is XML?

- XML stands for Extensible Markup Language

- If you've ever seen HTML you'll see the similarity

- The purpose of XML as I understand it is to transport, store, and structure data.

XML seems to be getting bigger and bigger. It is everywhere you turn. Apparently it is a great way to structure data and pull/index data. My use for it was in a XML datatype column. It was an IP Address Management database and it was being used to track different devices such as printers, switches, routers, desktops, servers, etc...As such, these different devices types have different properties that needed to be tracked. The best solution was an XML datatype in SQL 2005 that defines the data for each of these devices.

Please feel free to make any corrections or post comments and I will make corrections. I am certainly not an XML expert.

Below is some sql code that I used to query a table...hopefully it will help someone out I wish I had the TSQL before I started.

Here is the csv that was exported from a test table I had setup to test.

Please feel free to post any comments or questions...Enjoy your XML experience!!!

-- Selects all columns
SELECT * FROM TEAM

-- Selects my xml datatype column
SELECT TeamDoc.query('/Device')
FROM Team
WHERE TeamDoc.exist ('/Device') = 1

-- Replaces the device where @name equals "P29sdas" and changes @name to "PJSS2234"
UPDATE Team
SET TeamDoc.modify('
 replace value of
 (/Device/@name)[1]
 with "PJSS2234"')
WHERE TeamDoc.exist ('/Device') = 1 AND TeamDoc.exist('/Device[@name="P29sdas"]')=1

-- Pulls only the @name value from the TeamDoc column using query as XML
SELECT TeamDoc.query('data(/Device/@name)') As Name
FROM Team
WHERE TeamDoc.exist ('/Device') = 1

-- Pulls only the @name value from the TeamDoc column using value as text value
SELECT TeamDoc.value('data(/Device/@name)[1]','nvarchar(255)') As Name
FROM Team
WHERE TeamDoc.exist ('/Device') = 1

-- Pulled all the items from the XML column
Select TeamID, 
  TeamDoc.query('data(/Device/@type)') As Type,
  TeamDoc.query('data(/Device/@name)') As Node_Name,
  TeamDoc.query('data(/Device/@manufacturer)') As Manufacturer
FROM Team
WHERE TeamDoc.exist ('/Device') = 1

-- Something you might have in a stored procedure...this was just a test nothing special here

Declare @x xml
Set @x = (SELECT TeamDoc
FROM Team
WHERE TeamDoc.exist ('/Device') = 1
FOR XML AUTO)

SELECT @x

SELECT Team.TeamDoc.value('@name','VARCHAR(25)')AS Node_Name,
  Team.TeamDoc.value('@type','VARCHAR(25)')AS Node_Type,
  Team.TeamDoc.value('@manufacturer','VARCHAR(25)')AS Manufacturer
FROM @x.nodes('/Team/TeamDoc/Device') Team(TeamDoc)
GO

Some links you might find useful(I borrowed some parts of the example from the first link...:

1. http://www.code-magazine.com/article.aspx?quickid=0605081&page=1
2. http://www.w3schools.com/xml/xml_whatis.asp
3. http://en.wikipedia.org/wiki/XML

Dave's IT Blog

Thursday, February 21, 2008

XML What's All That About Anyway?

No comments:

Blog Archive