XML Structure in SQL Server

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

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

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

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.