Geek asks and answers

XML structure in SQL Server

I have the following code that 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 accepts when I provide it with the following structure and returns ssame results:

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

Please note that I do not have a root element.

My question is: what do I need to change so that the code accepts the first structure as valid and rejects the other?

Thank,

TheBlueSky

+3
source share
3 answers

If you want to request only one Txnode (first), you can do this

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

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

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

, xml, node. sp_xml_preparedocument , .

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

XML, XML- SQL Server, XML .

Go grab XSD-SQL Server XML Schema Collections PDF,

+2

The root element is one and only once. 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 the first case, you get 1, in the second 2.

0
source

More articles:


All Articles