SQL 2005: A Sample of Using XQuery

XQuery is a new language for querying XML data that allows navigational access based on XPath 2.0. Today I would like to give you a very simple XQuery sample. In this sample I will show you how to pass multi-valued parameter to a stored procedure. The stored procedure uses XQuery to get a list of values passed in.

    -- create a table
    CREATE TABLE [dbo].[student](
             [id] [int] NOT NULL,
             [name] [nvarchar](50),
             [ssn] [varchar](11),
             CONSTRAINT [PK_student] PRIMARY KEY CLUSTERED 
            (
                [id] ASC
            ) ON [PRIMARY]
    GO

    -- add a few rows for testing
    INSERT student VALUES(1, 'andrew',  '201-98-9238')
    INSERT student VALUES(2, 'lindsay', '656-89-9238')
    INSERT student VALUES(3, 'david',   '555-22-1111')
    GO

    -- create a stored procedure
    CREATE PROCEDURE get_students
    (
    	@ids AS XML
    )
    AS
    BEGIN
    	SELECT *
    	FROM student
    	WHERE id IN (SELECT xref.value('.', 'int') FROM @ids.nodes('/root/id') R(xref))
    END

    -- call the stored procedure passing in an xml parameter node
    DECLARE @ids xml
    SET @ids = 
    '<root>
    	<id>1</id>
    	<id>3</id>
    </root>'

    EXEC get_students @ids

XQuery implemented in SQL 2005 has opened a whole new world for T-SQL programming.

See also

THIS POST IS PROVIDED "AS-IS" WITH NO WARRANTIES AND CONFERS NO RIGHTS. Build time: Sun 03/30/2008 . ©2007 Dalun Software. All rights reserved. Back to Article List