August 4, 2008 at 8:44 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...
managerNumbermanagerFirstNamemanagerSurnameidemployeeIdemployeeFirstNameEmployeeSurnameemployeeJob
258664JohnSmith1456774AndrewJonesDBA
258664JohnSmith2456883RichardBakerDeveloper
258664JohnSmith3456336StephenBrownIT Support
258664JohnSmith4456105TomRobertsAdministrator
...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 2:46 pm
First of all, before posting check if XML is valid (XML Notepad or even VS editor will do the check).
I believe that you've got number of entries in 'docs' table, so using OPENXML will not be an option.
I would suggest looking into nodes() function, as query() will always return XML, not the rowset which you would expect.
SELECT T.ref.value('employeeName[1]/first[1]','nVarchar(100)') EmployeeFirstName
, T.ref.value('employeeName[1]/surname[1]','nVarchar(100)') EmployeeSurname
, T2.ref.value('first[1]','nVarchar(100)') ManagerFirstName
, T2.ref.value('surname[1]','nVarchar(100)') ManagerSurname
FROM DOCS
CROSS APPLY DOCS.xcol.nodes('/workingGroup/employeeGroup/employee') T(ref)
CROSS APPLY DOCS.xcol.nodes('/workingGroup/managerName') T2(ref)
August 6, 2008 at 5:54 am
Thanks Max... 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply