Re: OPENXML Sql 2000 to XQuery Sql 2005

A small performance tip: moving the join into an exist() method is often
more performant:

SELECT E.* from #Employee E
CROSS APPLY @xdata.nodes('/offices/office') AS OS(O)
WHERE 1 = OS.O.exist('@id[.=sql:column("E.OfficeID")]')

Best regards
Michael

"Dan Sullivan" <danATpluralsight.com> wrote in message
news:964a9ae6fcd58c864ced429a2f6@xxxxxxxxxxxxxxxxxxxxx
Just 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





.

Relevant Pages

  • Re: OPENXML Sql 2000 to XQuery Sql 2005
    ... Just to approximate your employee table: ... declare @XmlDocument int ... exec sp_xml_preparedocument @XmlDocument output, '<office ... -- declare @Xml xml ...
    (microsoft.public.sqlserver.xml)
  • Re: Help w/Self-Join Hierarchy Query
    ... > EXPLICIT then I may find the answer in there. ... > Then there is the ever popular employee - manager adjacency. ... > FOR XML SELF ...
    (microsoft.public.sqlserver.programming)
  • Stand alone Shredding in SQL Server 2005
    ... what I've read it will have to be some type of bulk processing. ... from the command line or bulk insert from T-SQL. ... the xml I want to shred: ...
    (microsoft.public.sqlserver.xml)
  • Re: Lets hear why This One couldnt do her Job.
    ... It appears to be a uniforms issue. ... a cross necklace at work at Heathrow but was allowed back to work after the ... He is not an employee of the health authority. ... for her would be on religious grounds and that would set a precedent ...
    (talk.origins)
  • Re: Deserialize Business Object
    ... Dim Employee As Employee = CType, ... I am having an issue deserializing a business object from a SQL stored ... As the XML being returned is an XML fragment due to not having a single ... Create a new XmlDocument, add the root element to it, then ...
    (microsoft.public.dotnet.xml)
Loading