January 23, 2012 at 8:34 pm
Hello all.
New to XML and any help is appreciated.
Code:
IF OBJECT_ID('tempdb..#TableExample') IS NOT NULL
DROP TABLE #TableExample
GO
CREATE TABLE #TableExample
(
XMLData xml
)
INSERT INTO #TableExample
VALUES
(
'<?xml version="1.0" encoding="ISO-8859-1"?>
<Directory>
<Groups GroupName = "Management">
<EmpID>45612</EmpID>
<EmpID>84512</EmpID>
<EmpID>32247</EmpID>
<EmpID>91548</EmpID>
<EmpID>64656</EmpID>
<EmpID>32487</EmpID>
</Groups>
<Groups GroupName = "ITDEV">
<EmpID>47312</EmpID>
<EmpID>88451</EmpID>
<EmpID>48165</EmpID>
<EmpID>34567</EmpID>
</Groups>
</Directory>
')
SELECT
R.N.query('.').value('(/Groups/EmpID/.)[1]','int') AS CustomerID
FROM
#TableExample TE
CROSS APPLY
TE.Xmldata.nodes('/Directory/Groups') R(N)
I'm able to pull the top EmpID from each Group, but I actually need to do 2 things.
Each EmpID(# can vary) and it's respective GroupName.
I've searched hours for a solution, but no luck.
Output to be something like this:
Department EmployeeID
-------------------------
Management 45612
Management 84512
Management 32247
Management 91548
Management 64656
Management 32487
ITDEV 47312
ITDEV 88451
ITDEV 48165
ITDEV 34567
January 24, 2012 at 3:05 pm
Take the output of your first .nodes() method and use to both get the group name, and feed another .nodes() call to turn its children into a resultset:
SELECT R.N.value('@GroupName', 'varchar(32)') AS [Department],
E.eid.value('.', 'int') AS [EmployeeID],
R.N.query('.') AS [Outer XML Node],
E.eid.query('.') AS [Inner XML Node]
FROM #TableExample TE
CROSS APPLY TE.Xmldata.nodes('/Directory/Groups') R(N)
CROSS APPLY R.N.nodes('EmpID') E(eid)
Eddie Wuerch
MCM: SQL
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply