本文介绍了如何将OPENXML数据联接到我的内部联接查询中?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
OpenXML:
DECLARE @idoc int DECLARE @doc varchar(1000) SET @doc = '<ROOT> <Employee EmployeeID = "1" EmpStatus = "Full Time"/> <Employee EmployeeID = "2" EmpStatus ="Part Time" /> </ROOT>' EXEC sp_xml_preparedocument @idoc OUTPUT, @doc SELECT * FROM OPENXML (@idoc, '/ROOT/Employee',1) WITH (EmployeeID varchar(10), EmpStatus varchar(20))结果:
EmployeeID EmpStatus 1 Full Time 2 Part Time表查询:
SELECT hr.EmployeeID, hr.Title, c.FirstName,c.LastName FROM HumanResources.Employee hr WITH (NOLOCK) INNER JOIN ContactInfo c WITH (NOLOCK) ON hr.ContactID = c.ContactID Where hr. EmployeeID IN ( 1, 2)结果:
EmployeeID Title FirstName LastName 1 Engineering Mike Brown 2 Programmer Yves Anthony如何使用EmployeeID将OpenXML数据连接到我的内部联接查询中?
How to join OpenXML data to my inner join query using EmployeeID?
推荐答案您是否坚持使用OpenXML?很古老,很古老-使用本机XQuery函数通常容易得多.
Do you insist on using OpenXML? It's old, it's legacy - using the native XQuery functions typically is much easier.
尝试这样的事情:
DECLARE @Employees TABLE (EmployeeID INT, Title VARCHAR(20), FirstName VARCHAR(20),LastName VARCHAR(20)) INSERT INTO @Employees VALUES(1, 'Engineering', 'Mike', 'Brown') INSERT INTO @Employees VALUES(2, 'Programmer', 'Yves', 'Anthony') DECLARE @doc XML SET @doc = '<ROOT> <Employee EmployeeID = "1" EmpStatus = "Full Time"/> <Employee EmployeeID = "2" EmpStatus ="Part Time" /> </ROOT>' ;WITH XmlCTE AS ( SELECT EmpID = Empl.value('@EmployeeID', 'int'), EmpStatus = Empl.value('@EmpStatus', 'varchar(10)') FROM @doc.nodes('/ROOT/Employee') AS Tbl(Empl) ) SELECT e.*, x.EmpStatus FROM @Employees e INNER JOIN xmlcte x ON e.EmployeeID = x.EmpID这给了我输出:
更多推荐
如何将OPENXML数据联接到我的内部联接查询中?
发布评论