Can't read data out of XML stored in table column

Question

I have some data stored in a table in XML format:

<AccountTypes xmlns="">
 <Id>1003</Id>
 <Id>2</Id>
 <Id>3</Id>
 <Id>1004</Id>
 <Id>1002</Id>
 <Id>0</Id>
</AccountTypes>

This is legacy data that was previously only processed in C# code so can't be changed.

I now need to read this and process it in a stored procedure. So far I've got the following code:

DECLARE @accountTypes XML
SELECT @accountTypes = DT.AccountTypes FROM dbo.DataTable AS DT
WHERE DT.Id = 1016
DECLARE @hdoc int
 
EXEC sys.sp_xml_preparedocument @hdoc OUTPUT, @accountTypes
SELECT *
FROM OPENXML (@hdoc, '/AccountTypes/Id')
WITH(
 Id VARCHAR(10) 'Id'
 )
EXEC sys.sp_xml_removedocument @hdoc

However the output from this is just rows of nulls:

Id
NULL
NULL
NULL
NULL
NULL
NULL

rather than the id values I was expecting.

I get the same results whether I include the colpattern ('Id') or not or whether I set the flags parameter on the OPENXML command.

Is there anything I can do with the data in it's current form to get the actual values out of the XML?

Answer 1

Id is looking for a sub element of the '/AccountTypes/Id' nodes that is called Id

i.e. would match something in

<AccountTypes xmlns="">
 <Id>
 <Id>1003</Id>
 </Id>
</AccountTypes>

You can use

DECLARE @accountTypes XML
SELECT @accountTypes = '<AccountTypes xmlns="">
 <Id>1003</Id>
 <Id>2</Id>
 <Id>3</Id>
 <Id>1004</Id>
 <Id>1002</Id>
 <Id>0</Id>
</AccountTypes>'
DECLARE @hdoc int
 
EXEC sys.sp_xml_preparedocument @hdoc OUTPUT, @accountTypes;
SELECT *
FROM OPENXML (@hdoc, '/AccountTypes/Id')
WITH(
 Id INT '.'
 )
EXEC sys.sp_xml_removedocument @hdoc

• ChrisF: Cheers. The annoying thing is that I'd started out with declaring Id as INT in the WITH statement but when that produced NULLs I thought it was due to the types being wrong so I went with VARCHAR instead. If I'd found the colPattern definition first I might have got there myself.

Answer 2

There's an alternative way to get the data:

DECLARE @accountTypes XML
SELECT @accountTypes = DT.AccountTypes FROM dbo.DataTable AS DT
WHERE DT.Id = 1016
SELECT Id.value('.','int') AS Id
FROM @accountTypes.nodes('/AccountTypes/Id') AS Ids(Id)

This produces the same results and as the data I have is quite small runs in virtually the same time. There may be differences between the queries when run on larger XML.