XML Structure in SQL Server

Question

I have the following code which works fine and returns the expected results:

DECLARE @xmlList xml
SET @xmlList = '<Tx><T>1</T><T>2</T><T>3</T></Tx>'

SELECT
        X.Y.value('.', 'varchar(10)') AS [ID], 'OK' AS [Status]
    FROM @xmlList.nodes('/Tx/T') X(Y)

However, it also accept when I provide it with the following structure and returns the ssame results:

SET @xmlList = '<Tx><T>1</T></Tx><Tx><T>2</T><T>3</T></Tx>'

Notice how I don't have a root element.

My question is, what do I need to change to make the code accept the first structure as valid and reject the other?

Thanks,

TheBlueSky

Mikael Eriksson · Accepted Answer · 2011-05-17 07:00:45Z

4

If you are only want to query one Tx node (the first) you can do like this

SELECT
  X.Y.value('.', 'varchar(10)') AS [ID], 'OK' AS [Status]
FROM @xmlList.nodes('/Tx[1]/T') X(Y)

You could also check the number of root nodes and call raiserror if you have more than one root.

select @xmlList.query('count(/Tx)').value('.', 'int')

The technique used before the xml datatype only accepted one root node. sp_xml_preparedocument will raise and exception if there are more than one root.

declare @idoc int
exec sp_xml_preparedocument @idoc out, @xmlList
exec sp_xml_removedocument @idoc

answered May 17, 2011 at 7:00

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

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

1 Comment

TheBlueSky Over a year ago

It's kind of doing what I want to achieve although not directly. I'll accept the answer are T-SQL XML data type doesn't seem to support that natively.

marc_s · Accepted Answer · 2011-05-17 04:56:01Z

2

If you want to enforce a specific structure of your XML, you can add a XML schema to your SQL Server database to check XML contents against a schema.

Go grab The Art of XSD - SQL Server XML Schema Collections as a free PDF download to learn more about this

answered May 17, 2011 at 4:56

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

3 Comments

AakashM Over a year ago

Do we have to go all the way to the sledgehammer cabinet and whip out XSD if all we want to do is establish that there is a single root element?

marc_s Over a year ago

@AakashM: unfortunately, there is no easy, predefined way in SQL Server to test for XML well-formed-ness :-(

TheBlueSky Over a year ago

This solution is too complex for my requirement. Thanks for the information though.

Dalex · Accepted Answer · 2011-05-17 06:36:41Z

0

Root element is one and only one time. So in your example:

; With c as(
SELECT
        X.Y.value('.', 'varchar(10)') AS [ID], 'OK' AS [Status]
    FROM @xmlList.nodes('//Tx') X(Y))
    SELECT COUNT(*) from c

In first case you will get 1, in second 2.

answered May 17, 2011 at 6:36

Dalex

3,63522 silver badges25 bronze badges

1 Comment

TheBlueSky Over a year ago

This is not at all what I was looking for.

Collectives™ on Stack Overflow

XML Structure in SQL Server

3 Answers 3

1 Comment

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related