Hi,

I am trying to create a temp table from an XML input. For whatever reason, it's only returning the first XML node in the table. Help as to why this is happening would be great!!

XML:
'<searchTerms><term>a</term><term>b</term><term>c</term></searchTerms>'

SQL:
ALTER PROCEDURE [dbo].[sp_BasicSearch]
@searchXML XML
AS
BEGIN
SET NOCOUNT ON;

--declare handle
DECLARE @handle INT
--prepare xml document
EXEC sp_xml_preparedocument @handle OUTPUT, @searchXML

SELECT * INTO #xml
FROM OPENXML (@handle, 'searchTerms')
WITH (term varchar(100) 'term')

SELECT * FROM #xml
RETURN
END

Result: 'a'