August 4, 2008 at 9:28 am
I have xml stored in xml data type's in Sql Server 2005 in the following format...
## see attachment ##
...and i want to tsql query it to output into a table...
managerNumber managerFirstName managerSurname id employeeId employeeFirstName EmployeeSurname employeeJob
258664 John Smith 1 456774 Andrew Jones DBA
258664 John Smith 2 456883 Richard Baker Developer
258664 John Smith 3 456336 Stephen Brown IT Support
258664 John Smith 4 456105 Tom Roberts Administrator
...I realise I can query manager details using...
SELECT xCol.query('data(/workingGroup/managerName/employeeNumber)') FROM docs
...and to get employee details using...
xCol.query('data(/workingGroup/employeeGroup/employee[@id=1]/employeeNumber)') FROM docs
...but how do I know the min and max of id or get the xquery to iterate through the elements. I am new to XML but have a good knowledge of Sql Server and tsql.
Any help is much appreciated.
August 4, 2008 at 9:44 am
Hi,
I think your xml file is not uploaded properly...
Please check once..and upload the correct one..
Cheers!
Sandy.
--
August 4, 2008 at 9:56 am
sorry... correct xml is now there...
August 5, 2008 at 6:06 am
Hi,
You can use this query.
DECLARE @Xml XML
SELECT @Xml = ' Your Xml here'
-----------
SELECT
tab.col.value('employeeNumber[1]','VARCHAR(20)')AS managerNumber,
tab.col.value('first[1]','VARCHAR(200)') AS managerFirstName,
tab.col.value('surname[1]','VARCHAR(200)') AS managerSurname,
tab1.col.value('./@id','VARCHAR(20)')AS id,
tab1.col.value('employeeNumber[1]','VARCHAR(20)')AS employeeId,
tab1.col.value('(employeeName/first)[1]','VARCHAR(20)')AS employeeFirstName,
tab1.col.value('(employeeName/surname)[1]','VARCHAR(20)')AS EmployeeSurname,
tab1.col.value('job[1]','VARCHAR(20)')AS employeeJob
FROM
@Xml.nodes('//workingGroup/managerName') tab(col),
@Xml.nodes('//workingGroup/employeeGroup/employee') tab1(col)
-- Output
---------------------------------------------------------------------------------------------------
--managerNumber managerFirstName managerSurname id
-- employeeId employeeFirstName EmployeeSurname employeeJob
--258664 John Smith 1 456774 Andrew Jones DBA
--258664 John Smith 2 456883 Richard Baker Developer
--258664 John Smith 3 456336 Stephen Brown IT Support
--258664 John Smith 4 456105 Tom Roberts Administrator
and use Where clause to filter your data as per your requirements...
Please let me know, If you have any concerns...
Cheers!
Sandy.
--
August 6, 2008 at 4:56 am
You may use this also.
with abc(managerNumber, managerFirstName, managerSurname, id, employeeId,
employeeFirstName, EmployeeSurname, employeeJob)
As
(SELECT
tab.col.value('employeeNumber[1]','VARCHAR(20)')AS managerNumber,
tab.col.value('first[1]','VARCHAR(200)') AS managerFirstName,
tab.col.value('surname[1]','VARCHAR(200)') AS managerSurname,
tab1.col.value('./@id','VARCHAR(20)')AS id,
tab1.col.value('employeeNumber[1]','VARCHAR(20)')AS employeeId,
tab1.col.value('(employeeName/first)[1]','VARCHAR(20)')AS employeeFirstName,
tab1.col.value('(employeeName/surname)[1]','VARCHAR(20)')AS EmployeeSurname,
tab1.col.value('job[1]','VARCHAR(20)')AS employeeJob
FROM
@B.nodes('//workingGroup/managerName') tab(col),
@B.nodes('//workingGroup/employeeGroup/employee') tab1(col) )
select min(employeeId) as MinID ,max(employeeId) as MaxID from abc
--where employeeID IN ('456883','456336')
Cheers!
Sandy.
--
August 6, 2008 at 5:51 am
Thanks Sandy,
works very nicely... 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply