Help to get data from XML column.

[bazyl]

I have a table with a XML column and appreciate any help how to get all data from that columns. This is the simplified sample, but should help to understand my issue. I can get all Employee data but I have no idea how to include DeptID data for each return row.

CREATE TABLE #Deductions (RecordID int identity(1,1) not null primary key, FirmID int, XML_Data xml not null)

DECLARE @TempXML xml

SET @TempXML =
'<Deductions startDate="01/01/2011" endDate="03/31/2011">
<Dept DeptID="561" TotalEmplyees="2" TotalDeductions="12345.55">
<Employee ID="5611"
Amount="1234.56"
EnrollDate="10/22/1999" />
<Employee ID="5612"
Amount="123.57"
EnrollDate="11/23/1997" />
</Dept>
<Dept DeptID="562" TotalEmplyees="3" TotalDeductions="54321.99">
<Employee ID="5621"
Amount="12.56"
EnrollDate="10/22/1999" />
<Employee ID="5622"
Amount="1.57"
EnrollDate="10/22/1995" />
<Employee ID="5623"
Amount="1.57"
EnrollDate="12/22/1991" />
</Dept>
</Deductions>'

INSERT INTO #Deductions(FirmID, XML_Data) VALUES(12345, @TempXML)

SELECT d.RecordID, d.FirmID,
R.e.value('@ID', 'int') ID,
R.e.value('@Amount', 'decimal(29,2)') Amount,
R.e.value('@EnrollDate', 'datetime') EnrollDate
FROM #Deductions d WITH (NOLOCK)
CROSS APPLY XML_Data.nodes ('/Deductions/Dept/Employee') R(e)
WHERE d.FirmID = 12345

DROP TABLE #Deductions

 

[gmmastros]

SELECT d.RecordID, d.FirmID,   
    R.e.value('@ID', 'int') ID,
    R.e.value('@Amount', 'decimal(29,2)') Amount,
    R.e.value('@EnrollDate', 'datetime') EnrollDate,
[!]    R.e.value('../@DeptID','int') DeptId[/!]
FROM #Deductions d WITH (NOLOCK)
  CROSS APPLY XML_Data.nodes ('/Deductions/Dept/Employee') R(e)
WHERE d.FirmID = 12345

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bazyl]

I really appreciate your help, thank you very much George.

 

[gmmastros]

You're welcome.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bazyl]

Hi George,

I need your help, please have a look at the SELECT query. I added child element <Contribution> to <Employee> one and now I cannot get ID, Amount, EnrollDate, and DeptId columns populated, they are populated with NULLs. Could you please help?

Thank you.

PS. How do you make you code to appear in the code box?

CREATE TABLE #Deductions (RecordID int identity(1,1) not null primary key, FirmID int, XML_Data xml not null)

DECLARE @TempXML xml
SET @TempXML = '
<Deductions startDate="01/01/2011" endDate="03/31/2011">
<Dept DeptID="561" TotalEmplyees="2" TotalDeductions="12345.55">
<Employee ID="5611" Amount="1234.56" EnrollDate="10/22/1999">
<Contribution>
<Deferral Type="Pre" Amount="100" Percent="6" />
<Deferral Type="Post" Amount="50" Percent="3" />
</Contribution>
</Employee>
<Employee ID="5612" Amount="123.57" EnrollDate="11/23/1997">
<Contribution>
<Deferral Type="Pre" Amount="75" Percent="9" />
<Deferral Type="Post" Amount="55" Percent="6" />
</Contribution>
</Employee>
</Dept>
<Dept DeptID="562" TotalEmplyees="1" TotalDeductions="321.99">
<Employee ID="5621" Amount="12.56" EnrollDate="10/22/1999">
<Contribution>
<Deferral Type="Pre" Amount="55" Percent="8" />
<Deferral Type="Post" Amount="45" Percent="5" />
</Contribution>
</Employee>
</Dept>
</Deductions>'

INSERT INTO #Deductions(FirmID, XML_Data)
VALUES(12345, @TempXML)

SELECT d.RecordID, d.FirmID,
R.e.value('../@ID', 'int') ID, --shows NULL
R.e.value('../@Amount', 'decimal(29,2)') Amount, --shows NULL
R.e.value('../@EnrollDate', 'datetime') EnrollDate, --shows NULL
R.e.value('../@DeptID','int') DeptId, --shows NULL
R.e.value('@Type','varchar(25)') [Type],
R.e.value('@Amount','varchar(25)') Amount,
R.e.value('@Percent','varchar(25)') [Percent]
FROM #Deductions d WITH (NOLOCK)
CROSS APPLY XML_Data.nodes ('/Deductions/Dept/Employee/Contribution/Deferral') R(e)
WHERE d.FirmID = 12345

DROP TABLE #Deductions
</!CODE>

 

[gmmastros]

Looking at your XML, you basically have this.
[tt]
Deductions
Dept
Employee
Contributions
Deferral
[/tt]

Looking at your code, you have this:
[tt]XML_Data.nodes ('/Deductions/Dept/Employee/Contribution/Deferral')[/tt]

The way I look at this is... we are starting at the Deferral node. If I want something from contributions, then I need to use '../@Whatever'. The ../ causes us to go up a node. Since you want stuff from 2 nodes up (the employee node), you need to use 2 ../ (like this)

    R.e.value('../../@ID', 'int') ID, --shows NULL
    R.e.value('../../@Amount', 'decimal(29,2)') Amount, --shows NULL
    R.e.value('../../@EnrollDate', 'datetime') EnrollDate, --shows NULL
    R.e.value('../../@DeptID','int') DeptId, --shows NULL

PS. How do you make you code to appear in the code box?

You use the code tags for that.

[ignore]

Put your code here

[/ignore]



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

There should have been another ../ for deptid.

    R.e.value('../../../@DeptID','int') DeptId, --shows NULL

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bazyl]

George,

Thank you very much, your help is priceless. I'm fighting with deadlines and too many 'new' think on this project, consequently 24 hours seams like not enough. I feel bad taking advantage of your good will and not putting enough effort to find solution on my own but I hope you understand.

Again, thank you.

SELECT 'It works!'

 

[gmmastros]

I do understand. I hope things get a little easier for you soon. I know what it's like to be stuck behind an unrealistic deadline. It's not fun!

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom