Parsing non-standard XML in SQL Server with XTbl

Question

I have a serialised piece of data in a column that I want to retrieve a single value from, in the following form:

<FirstNode>Something</><SecondNode>Something Else</>

I want to retrieve, for example, 'Something' from this in SQL Server. I've tried the following:

declare @data xml;
set @data = cast([my data source] as xml);

select @data.value('(/UserName)[1]','varchar(50)')

I'm probably way off with this, I don't have a huge deal of experience with parsing XML. Any help would be great.

Edit: I get the error

XML parsing: line 1, character 20, illegal qualified name character

That's not XML, no compliant XML parser should accept it. Ask whomever is sending it to you to send XML instead, or you'll have to write your own not-XML parser. — Dour High Arch
– Dour High Arch, Commented Nov 20, 2013 at 17:49
Describing this as non-standard XML is a bit like describing Fortran as non-standard Java. Forget any notion of using XML tools to process non-XML. Don't even think of it as XML. If you want to ask questions about it, don't tag your questions as "XML". — Michael Kay
– Michael Kay, Commented Nov 21, 2013 at 8:37

Louis Ricci · Accepted Answer · 2013-11-20 18:19:25Z

1

Just use the CHARINDEX and SUBSTRING functions to get the data you want. Rolling my example into a function would probably be your best bet.

DECLARE @tbl TABLE(data VARCHAR(MAX))
INSERT INTO @tbl VALUES
    ('<FirstNode>Something</><SecondNode>Something Else</>'),
    ('<SecondNode>Something Else</><FirstNode>More Something</>'),
    ('<BadNoe>Something</><SecondNode>Something Else</>')

DECLARE @fnd VARCHAR(64)
DECLARE @end VARCHAR(64)
SET @fnd = '<FirstNode>'
SET @end = '</>'

SELECT SUBSTRING(a.[data], a.[start] + LEN(@fnd), a.[end] - (a.[start] + LEN(@fnd)))
FROM (SELECT data [data], CHARINDEX(@fnd, data, 0) [start], CHARINDEX(@end, data, CHARINDEX(@fnd, data, 0)) [end] FROM @tbl) a
WHERE a.[start] > 0

answered Nov 20, 2013 at 18:19

Louis Ricci

21.1k5 gold badges50 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dan Over a year ago

After I realised there was no way XML parsing would work, this seemed like the next best thing. Nice clean solution, thanks.

Louis Ricci Over a year ago

@Dan - Just a note, this solution will fall apart if you have nested elements <a><b>1</><c>2</></> finding 'a' would return <b>1</> instead of <b>1</><c>2</>

Collectives™ on Stack Overflow

Parsing non-standard XML in SQL Server with XTbl

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related