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
>

openxml rowset problem

hi all,

I'm trying to get some xml data into sql server but i ran into this problem: openxml seems to repeat results ...

I simplified my example to this:

----------------
Declare @.rDoc int,
@.sDoc varchar(4000)

Set @.sDoc = '
<ROOT>
<Rider>aaa</Rider>
<Rider>bbb</Rider>
<Rider>ccc</Rider>
</ROOT>'

EXEC sp_xml_preparedocument @.rDoc OUTPUT, @.sDoc

SELECT *
FROM OPENXML (@.rDoc, 'ROOT/Rider', 1)
WITH (RiderName varchar(50) '../Rider')
------------------

it returns 3 records for the 'rider' elements, _but_ all the records contain the text 'aaa' :/

does anybody know the solution to this?

cheers,

alexIf you did the WITH statement below instead it would work - note also as a general practice you should avoid walking "up" the xml data structure (the '../Rider' construct) for performance reasons

Declare @.rDoc int,
@.sDoc varchar(4000)

Set @.sDoc = '
<ROOT>
<Rider>aaa</Rider>
<Rider>bbb</Rider>
<Rider>ccc</Rider>
</ROOT>'

EXEC sp_xml_preparedocument @.rDoc OUTPUT, @.sDoc

SELECT *
FROM OPENXML (@.rDoc, 'ROOT/Rider', 1)
WITH (RiderName varchar(50) '.')|||thanks alot! :)