Serializing Hashset contents to a SQL table

Question

I have a hashset that I want to serialize to a SQL Server table. When serialized hashset looks like this...

<InstallerContactIds>
   <int>153771</int> 
   <int>209572</int> 
</InstallerContactIds>

I am using the following to insert the XML into the table...

INSERT INTO dbo.cv_AssessorActionPlanInstallers
SELECT @AssessorActionPlanId, InstallerId
From OPENXML (@XmlDocumentHandle, 'AssessorActionPlan/InstallerContactIds', 2) 
With
(
    InstallerId int 'int'     
)

However, I only get the first id inserted into the table. Is it possible to insert all ids in this case?

I am using SQL Server 2005

Answer 1

You didn't specify what version of SQL you are using, but if you are on 2005 or newer, you can use the new XML data type.

declare @xml xml
select @xml = '
<InstallerContactIds>
   <int>153771</int> 
   <int>209572</int> 
</InstallerContactIds>'

select
    x.i.value('.', 'int')
from
    @xml.nodes('/InstallerContactIds/int') as x(i)

If you are stuck using OpenXML, it should work if you do this:

INSERT INTO dbo.cv_AssessorActionPlanInstallers
SELECT @AssessorActionPlanId, InstallerId
From OPENXML (@XmlDocumentHandle, 'AssessorActionPlan/InstallerContactIds/int', 2) 
With
(
    InstallerId int '.'
)