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

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.