Parsing XML Data from SQL Database Column

There are scenarios when we will have data dumped in to Database Column in XML format.

Now when we are reading those data it does not make sense to present the data in the XML format.

Here are the example where we have XML data in a below format.

declare @Employee as nvarchar(max)

declare @EmployeeXML as XML

declare @cnt int

   SET @EmployeeXML='<Employees>

   <Employee><EmployeeID>Anoj Singh</EmployeeID><EmployeeName>150025</EmployeeName><Department>Delivery</Department></Employee>

   <Employee><EmployeeID>Ashutosh Mund</EmployeeID><EmployeeName>150016</EmployeeName><Department>Sales</Department></Employee>

   <Employee><EmployeeID>Nihil Jain</EmployeeID><EmployeeName>150017</EmployeeName><Department>Finance</Department></Employee>

   <Employee><EmployeeID>Sravan Kumar</EmployeeID><EmployeeName>150018</EmployeeName><Department>Admin</Department></Employee>

   </Employees>'

SELECT b.value('(EmployeeID)[1]', 'NVARCHAR(200)')+','+

       b.value('(EmployeeName)[1]', 'NVARCHAR(200)')+','+

       b.value('(Department)[1]', 'NVARCHAR(200)') as EmployeeData

       FROM  @EmployeeXML.nodes('Employees/Employee') a(b)

       print @Employee

This will display the data as shown below.

There are other way and format also which I will share in subsequent posts.

Installing Complete TFS 2010 in Windows Server 2008 R2 with SharePoint

Installing TFS 2010 in Windows Server 2008 R2 with SharePoint 

(including SSRS, SSIS, and Build Server Configuration)

Error: 15138 – The database principal owns a schema in the database, and cannot be dropped

Error: 15138 – The database principal owns a schema in the database, and cannot be dropped

This is a typical issue faced by every Database guy if they are beginner to SQL DB world. This appears when we try to delete a database user who's owner of the database schema and SQL DB does not allow to delete the user if the user owns a schema.

Resolution to this is, a) Find out the schema or schema's owned by that user.

                                      b) Assign those schema to some other user

                                      c) Delete the user.

This can be done in 2 ways 1. Using SQL Script

                                                   2. Using Management Studio.

Using SQL Script

SELECT name FROM  sys.schemas WHERE principal_id = USER_ID('DBUser')

Run another command to assign the schema to another user

   

ALTER AUTHORIZATION ON SCHEMA::NameofSchema TO DBUser2 

GO

DROP USER DBUser

Using Management Studio

Step 1 : Select the User name and Open Property Window

Step 2 : Select the Schema Property viewed in above screen

Step 3 : Assign the Schema to another user by clicking Search and Browsing appropriate new user.

The most recommended is to assign to "dbo".

Step 4: Now if you see the new user property you can see the Schema is owned by the new user.

Now you can delete the user which you are intend to.