Hi,

I have been using the OPENXML function in SQL to insert, update and delete records. Now, I have a requirement to create a custom hierarchical XML within a Stored Proc but I cannot figure out how to do that.

Consider two tables: Employees and Departments

Employees: EmployeeID, EmployeeName, EmployeeSalary, EmployeeDetails, DeptID

Departments: DeptID, DeptName

(EmployeeID and DeptID are entity fields)

This is what the XML should look like:

<RECORD>

 <DEPTID DEPTID="1">
  <EMPLOYEE EMPID=”..” NAME=".." SALARY=".." DEPTNAME="..">
    <DETAILS>...<DETAIL>
  </EMPLOYEE>
  <EMPLOYEE EMPID=”..” NAME=".." SALARY=".." DEPTNAME="..">
    <DETAILS>...<DETAIL>
  </EMPLOYEE>
 </DEPTID>

 <DEPTID DEPTID="2">
  <EMPLOYEE EMPID=”..” NAME=".." SALARY=".." DEPTNAME="..">
    <DETAILS>...<DETAIL>
  </EMPLOYEE>
  <EMPLOYEE EMPID=”..” NAME=".." SALARY=".." DEPTNAME="..">
    <DETAILS>...<DETAIL>
  </EMPLOYEE>
 </DEPTID>

</RECORD>

I have created the select query that returns the recordset like that:

SELECT e.EmployeeID, e.EmployeeName, e.EmployeeSalary, e.EmployeeDetail, e.DeptID, d.DeptName
FROM Employees e INNER JOIN Departments d ON e.DeptID = d.DeptID
ORDER BY e.DeptID

I would appreciate your help.

Cheers

CPALL

You will need to use a FOR XML query. Are you using SQL Server 2000 as the forum indicates? If so then I think you'll need FOR XML EXPLICIT which is a real pain, with SQL 2005 you can use FOR XML PATH which is easier.

--

Joe (Microsoft MVP - XML)

Hi Joe,

I am using SQL Server 2000. Do you have any ideas on how to do that ?

Cheers

I have got a good start using this code below. However, I have a big problem. Since the Employees.EmployeeDetail field is of ntext type, I cannot use the keyword DISTINCT with it.

Does anyone know how to get around this?

I need this field to be ntext type because I will be storing xml in there.

SELECT DISTINCT
            Tag = 1,
            Parent = NULL,
            [deptid!1!id] = d.DeptID,
            [employee!2!id] = NULL,
            [employee!2!name] = NULL,
            [employee!2!salary] = NULL,
            [employee!2!Detail] = NULL,
            [employee!2!deptid] = NULL
FROM dbo.Departments d INNER JOIN dbo.Employees e ON d.DeptID = e.DeptID

Then you should be doing a Distinct excluding EmployeeDetail column and do a join with the Employees table on EmpoyeeID to get a work around for this.

Moreover I dont understand why you do a join there when you set all the other columns to NULL except the deptid.
_________________________
- Vijay G
Strive for Perfection

Hi Vijay G,

Many thanks for your reply.

This is the full working code if I use a nvarchar type field to store an xml. However, the nvarchar type is too small to store xml. Therefore, I will have to change it back to ntext but as I said before, with ntext I cannot use the distinct function.

I did not quite understand your suggestion. I would appreciate if you could have a look at my code and make the necessary changes for the distinct to work.

Cheers

CPALL

SELECT DISTINCT
            Tag = 1,
            Parent = NULL,
            [deptid!1!id] = d.DeptID,
            [employee!2!id] = NULL,
            [employee!2!name] = NULL,
            [employee!2!salary] = NULL,
            --[employee!2!Detail] = NULL,
            [employee!2!!xml] = NULL,
            [employee!2!deptid] = NULL

FROM Employees e INNER JOIN Departments d ON e.DeptID = d.DeptID

UNION ALL

SELECT DISTINCT
            Tag = 2,
            Parent = 1,
            [deptid!1!id] = d.DeptID,
        [employee!2!id] = e.EmployeeID,
            [employee!2!name] = e.EmployeeName,
            [employee!2!salary] = e.EmployeeSalary,
        e.EmployeeDetail as [employee!2!!xml],
            [employee!2!deptid] = e.DeptID

FROM Employees e INNER JOIN Departments d ON e.DeptID = d.DeptID

ORDER BY [deptid!1!id], [employee!2!deptid], [employee!2!!xml]