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.