Decode HTML Strings in SQL Server

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- EXEC UrlDecode '%c3%a1'
-- EXEC UrlDecode '\\Tul1ciprmw1\RichMediaAssets\Open\Telemundo\Al Rojo Vivo con Mar%c3%ada Celeste\_derived_jpg_q90_172x128_m1\3ARValx05Mar%c3%ada Celeste Arrar%c3%a1s.jpg'
-- EXEC UrlDecode '%4D'

ALTER PROCEDURE UrlDecode
-- Add the parameters for the stored procedure here
@URL varchar(2000)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @Position INT,
@Base CHAR(16),
@High TINYINT,
@Low TINYINT,
@NewHigh INT,
@Pattern CHAR(21)

SELECT @Base = '0123456789abcdef',
@Pattern = '%[%][0-9a-f][0-9a-f]%',
@Position = PATINDEX(@Pattern, @URL)

SELECT @High = CHARINDEX(SUBSTRING(@URL, @Position + 1, 1), @Base) -1,
@Low = CHARINDEX(SUBSTRING(@URL, @Position + 2, 1), @Base) -1

WHILE @Position > 0
BEGIN

IF (@High & 15) = 12 -- xC0
BEGIN
SELECT @NewHigh = @Low * POWER(2, 6) --Shift Low 6 bits

SELECT @High = CHARINDEX(SUBSTRING(@URL, @Position + 4, 1), @Base) -1
SELECT @Low = CHARINDEX(SUBSTRING(@URL, @Position + 5, 1), @Base) -1

SELECT @URL = STUFF(@URL, @Position, 6, CHAR(@NewHigh | (16 * @High) | @Low))

SELECT @High = 0, @Low = 0, @NewHigh = 0

END
ELSE
BEGIN
SELECT @URL = STUFF(@URL, @Position, 3, CHAR(16 * @High | @Low))

END

SELECT @Position = PATINDEX(@Pattern, @URL)
IF @Position > 0
BEGIN
SELECT @High = CHARINDEX(SUBSTRING(@URL, @Position + 1, 1), @Base) -1,
@Low = CHARINDEX(SUBSTRING(@URL, @Position + 2, 1), @Base) -1
END
END

SELECT @URL

END
GO

Selecting XPath Nodes

To select a range of nodes based on their position, use the really cool position() function.

Using the code example below, this syntax will get the first two nodes:

var nodeList = xmlDoc.selectNodes("/root/items/item[position()<3]");

XPath Basics

When querying an XML Document with XPath syntax, there are two things you need to identify; the node(s) you want to return and the criteria that determines which nodes qualify.

In this XML document:

<root>
   <items>
      <item>One</items>
      <item>Two</items>
      <item>Three</items>
   </items>
</root>

To get the item nodes using MSXML
var nodeList = xmlDoc.selectNodes("/root/items/item");

To apply selection conditions, add brackets:
var nodeList = xmlDoc.selectNodes("/root/items/item[.='One']");

The "." in the brackets means "this node". So in plain English you're saying, "Give me all the item nodes where the value is "One".

You can also select nodes based on the values of child or parent nodes.

Let's make the simple example above a little more complicated:

<root>
   <items>
      <item>One</item>
         <type>Odd Number</type>
      <item>Two</item>
         <type>Even Number</type>         
      <item>Three</item>
         <type>Odd Number</type>
   </items>
</root>

To get all the nodes which have a type value of "Odd Number" you would use this syntax:

var nodeList = xmlDoc.selectNodes("/root/items/item[./type='Odd Number']");

Notice here that the select path still goes to the item level. However, the query references the type node. The XPath path symbols are nearly identical to directory structure navigation syntax.

/ = levels
. = current node
.. = parent node
./ = child of the current node