LISTING 2: Stored Procedure That Uses 
OpenXML with Namespaces

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = 
object_id(N'[dbo].[ViewUsingNamespace]') 
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[ViewUsingNamespace]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE ViewUsingNamespace @xmlDoc ntext
AS
DECLARE @doc int
DECLARE @cnt int

BEGIN CALLOUT A
-- Parse the XML document into memory.
EXEC sp_xml_preparedocument  @doc output,
 @xmlDoc, '<root xmlns:b="urn:myBooks" 
xmlns:p="urn:myPerson"/>'
END CALLOUT A

BEGIN CALLOUT B
SELECT *
FROM OpenXML(@doc,'//b:book',0)
WITH (
      book_title varchar(10) './/b:title',
      title      varchar(2)  './/p:title',
      firstname  varchar(50) './/p:firstName',
      lastname   varchar(50) './/p:lastName'
     )
END CALLOUT B

-- Always remove the document from memory.
EXEC sp_xml_removedocument @doc

-- Return(@cnt).

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO