convert json to xml

I found this stored procedure at http://sqldom.sourceforge.net

I had to share

 

ALTER PROCEDURE [dbo].[sputilConvertJSONToXML]
@JSON nvarchar(MAX),
@XML xml OUTPUT
AS
BEGIN
DECLARE @tvStack TABLE (
StackID int IDENTITY PRIMARY KEY,
Tag varchar(8000),
IsArrayElem bit
)

DECLARE @I int
DECLARE @C char
DECLARE @LastChar char

DECLARE @Buf varchar(8000)
DECLARE @XMLStr varchar(MAX)
DECLARE @Tag varchar(8000)

DECLARE @StackID int

DECLARE @InQuote bit
DECLARE @EndedQuote bit
DECLARE @IsArrayElem bit

SET @I = 1
SET @InQuote = 0

SET @XMLStr = ''
SET @Buf = ''

WHILE @I < LEN(@JSON + 'x') - 1 BEGIN
IF @C NOT IN (CHAR(9), CHAR(10), CHAR(13), ' ') SET @LastChar = @C

SET @C = SUBSTRING(@JSON, @I, 1)

IF @C = '"' BEGIN
--Found Quote
IF @EndedQuote = 1 BEGIN
--Just exited a quote: special case for embedded ""
SET @Buf = @Buf + @C
SET @InQuote = 1
SET @EndedQuote = 0
END
ELSE IF @InQuote = 1 BEGIN
--We were already in a quote, so we must be exiting
SET @InQuote = 0
SET @EndedQuote = 1
END
ELSE BEGIN
SET @InQuote = 1
END
END
ELSE BEGIN
--not a quote character

SET @EndedQuote = 0
IF (@InQuote = 1) BEGIN
--just append character
IF @C NOT IN (CHAR(9), CHAR(10), CHAR(13)) BEGIN
SET @Buf = @Buf +
CASE @C
WHEN '' THEN '>'
WHEN '&' THEN '&'
ELSE @C
END
END
END
ELSE BEGIN
--inspect character to determine state

IF @C = ':' BEGIN
--@Buf contains VarName
SET @XMLStr = @XMLStr + ''

INSERT INTO @tvStack (Tag) VALUES (@Buf)

SET @Buf = ''
END
ELSE IF @C = ',' BEGIN
--@Buf contains VarValue
IF @Buf '' BEGIN
SET @XMLStr = @XMLStr + @Buf
SET @Buf = ''

--pop tag from stack and write closing tag to XML
SET @Tag = ''
SELECT TOP 1 @Tag = Tag, @StackID = StackID FROM @tvStack ORDER BY StackID DESC
DELETE FROM @tvStack WHERE StackID = @StackID

IF @Tag '' BEGIN
SET @XMLStr = @XMLStr + ''
END
END

--We are on a comma. If the top element is an array element, peek and write
--a close tag and a re-open tag to XML
SET @IsArrayElem = 0
SELECT TOP 1 @IsArrayElem = IsArrayElem, @Tag = Tag FROM @tvStack ORDER BY StackID DESC
IF @LastChar = '}' AND @IsArrayElem = 1 BEGIN
SET @XMLStr = @XMLStr + '' + ''
END

END
ELSE IF @C = '[' BEGIN
--Start of array.

--peek at stack and add first array element tag
SET @Tag = ''
SELECT TOP 1 @Tag = Tag, @StackID = StackID FROM @tvStack ORDER BY StackID DESC

IF @Tag '' BEGIN
SET @Tag = @Tag + '_'

--push array element tag to stack and write closing tag to XML
INSERT INTO @tvStack (Tag, IsArrayElem) VALUES (@Tag, 1)
SET @XMLStr = @XMLStr + ''
END
END
ELSE IF @C = '}' BEGIN
--at end of object

--pop tag from stack and write closing tag to XML
SELECT TOP 1 @Tag = Tag, @StackID = StackID FROM @tvStack ORDER BY StackID DESC
DELETE FROM @tvStack WHERE StackID = @StackID

IF @Tag '' BEGIN
SET @XMLStr = @XMLStr + @Buf + ''
END
SET @Buf = ''
END
ELSE IF @C = ']' BEGIN
SELECT TOP 1 @Tag = Tag, @StackID = StackID FROM @tvStack ORDER BY StackID DESC
DELETE FROM @tvStack WHERE StackID = @StackID

IF @Tag '' BEGIN
SET @XMLStr = @XMLStr + @Buf + ''
END
SET @Buf = ''
END
ELSE BEGIN
IF @C NOT IN (CHAR(9), CHAR(10), CHAR(13), '{') BEGIN
SET @Buf = @Buf +
CASE @C
WHEN '' THEN '>'
WHEN '&' THEN '&'
WHEN ' ' THEN ''
ELSE @C
END
END
END

END
END

SET @I = @I + 1
END

--pop any remaining tags from stack
WHILE EXISTS(SELECT StackID FROM @tvStack) BEGIN
SET @Tag = ''
SELECT TOP 1 @Tag = Tag, @StackID = StackID FROM @tvStack ORDER BY StackID DESC
DELETE FROM @tvStack WHERE StackID = @StackID
IF @Tag '' BEGIN
SET @XMLStr = @XMLStr + ''
END
END

SET @XML = NULLIF(RTRIM(@XMLStr), '')
END