OPENXML Sql 2000 to XQuery Sql 2005

I am trying to replace the OPENXML in the following SQL code with SQL Server
2005 XQuery, but I don't know how to specify a non-XML rowset in the JOIN
using the XML data type. Can you please help?
declare @.XmlDocument int
exec sp_xml_preparedocument @.XmlDocument output, '<offices><office
id="1"/><office id="2"/></offices>'
-- instead of @.XmlDocument, I want to have:
-- declare @.Xml xml
-- select @.Xml = '<offices><office id="1"/><office id="2"/></offices>'
-- get employees from offices 1 and 2
select *
from Employee
join openxml(@.XmlDocument, '//offices/office', 1) with (OfficeID int '@.id')
OfficeXml on
Employee.OfficeID = OfficeXml.OfficeID
exec sp_xml_removedocument @.XmlDocumentJust to approximate your employee table:
CREATE TABLE #employee
(
id int IDENTITY PRIMARY KEY,
OfficeID INT
)
INSERT INTO #employee VALUES (1)
INSERT INTO #employee VALUES (2)
INSERT INTO #employee VALUES (2)
INSERT INTO #employee VALUES (1)
INSERT INTO #employee VALUES (3)
INSERT INTO #employee VALUES (3)
Then use OPENXML to have something to compare to:
declare @.XmlDocument int
exec sp_xml_preparedocument @.XmlDocument output, '<offices><office
id="1"/><office id="2"/></offices>'
-- instead of @.XmlDocument, I want to have:
-- declare @.Xml xml
-- select @.Xml = ''
-- get employees from offices 1 and 2
select *
from #Employee E
join openxml(@.XmlDocument, '//offices/office', 1) with (OfficeID int '@.id')
OfficeXml on
E.OfficeID = OfficeXml.OfficeID
exec sp_xml_removedocument @.XmlDocument
-- Then CROSS APPLY and nodes
-- to pull office id's out of xml and join
-- with employee tables as was done with OPENXML
DECLARE @.xdata xml
SET @.xdata = '<offices><office id="1"/><office id="2"/></offices>'
SELECT E.* from #Employee E
CROSS APPLY @.xdata.nodes('/offices/office') AS OS(O)
WHERE E.OfficeID = OS.O.value('@.id', 'INT')
Dan

> declare @.XmlDocument int
> exec sp_xml_preparedocument @.XmlDocument output, '<offices><office
> id="1"/><office id="2"/></offices>'
> exec sp_xml_removedocument @.XmlDocument
>