2

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?

2 Answers 2

5

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
1
  • 1
    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.
    – ChrisF
    Jan 6, 2023 at 12:31
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.