[RESOLVED] Help, only returning first result

[mulhearn22]

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'

[szlamany]

I know very little about XML - but after looking at the books-online for OPENXML I modified your xml-string a bit to look like this

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='<searchTerms><term termID="A"></term><term termID="B"></term></searchTerms>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT    *
FROM       OPENXML (@idoc, '/searchTerms/term',1)
            WITH (termID  varchar(10))

and it returns

termID     
---------- 
A
B

(2 row(s) affected)

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/8088b114-7d01-435a-8e0d-b81abacc86d6.htm

[mulhearn22]

Thank you! That worked. Much appreciated!

[szlamany]

This also works - and respects the original XML string.

You need to specify the node "/term" and use a flags value of 1 and then ask for text() in the column pattern.

Create PROCEDURE [dbo].[sp_BasicSearch]
@searchXML XML
AS
BEGIN
SET NOCOUNT ON;
select @searchxml
--declare handle
DECLARE @handle INT
--prepare xml document
EXEC sp_xml_preparedocument @handle OUTPUT, @searchXML

select * FROM OPENXML (@handle, '/searchTerms/term',1)
WITH (term varchar(100) 'text()')

RETURN
END


go

Exec [dbo].[sp_BasicSearch] '<searchTerms><term>a</term><term>b</term><term>c</term></searchTerms>'

this returns

term                                                                                                 
---- 
a
b
c

This is pretty complicated stuff - the options available are extensive but seem well documented.

Good luck!