In This post Let we see some important question and answers in Sql Server, which is a continuation of previous post.
Xml format:
-- or
52. What is sparse columns ?
A Sparse column is a optimised storage format to store NULL values, we can specify a table column as sparse by SPARSE attribute.
- It greatly reduces the space requirement for your table columns.
- It doesn't take up any space when the column value contains the NULL.
- It takes up more space when column contains NON-NULL value
- It is suitable for only the columns which have storage value NULL in large percentage.
CREATE TABLE employeetable
(
id INT IDENTITY(1,1),
NAME VARCHAR(40),
addres VARCHAR(200),
tempaddres VARCHAR(300) SPARSE
)
53. How to modify the value in XML ?
Now below example will explain you how to change the value of an Hr to tech in an xml first tag. [1] Refers the occurrence of first element.
Xml Format:
<Emps>
<Emp dept="HR">a</Emp>
<Emp dept="HR" />
<Emp dept="tech">R</Emp>
</Emps>
DECLARE @employeedata XML
DECLARE @deletecondition VARCHAR(20) = 'HR'
--xml
SELECT @employeedata = N'<Emps><Emp dept="HR">a</Emp><Emp dept="HR"></Emp><Emp dept="tech">R</Emp></Emps>'
SET @employeedata.modify('replace value of (/Emps/Emp[@dept=("HR")]/@dept)[1] with "Tech"')
SELECT @employeedata
Output:
<Emps>
<Emp dept="Tech">a</Emp>
<Emp dept="HR" />
<Emp dept="tech">R</Emp>
</Emps>
54. How to insert a new tag in existing XML ?
Now we see how to insert an new element in the existing xml , in different positions.
Xml format:
<Emps>
<Emp dept="HR">a</Emp>
<Emp dept="HR" />
<Emp dept="tech">R</Emp>
</Emps>
DECLARE @employeedata XML
DECLARE @deletecondition VARCHAR(20) = 'HR'
--xml
SELECT @employeedata = N'<Emps><Emp dept="HR">a</Emp><Emp dept="HR"></Emp><Emp dept="tech">R</Emp></Emps>'
SELECT @employeedata
--Insertion
SET @employeedata.modify('insert <Emp>b</Emp> as first into (/Emps)[1]')
-- or
SET @employeedata.modify('insert <Emp>b</Emp> into (/Emps/Emp)[1]') -- insert as last tag
SELECT @employeedata
<Emps>
<Emp>b<Emp>b</Emp></Emp>
<Emp dept="HR">a</Emp>
<Emp dept="HR" />
<Emp dept="tech">R</Emp>
</Emps>
55. How to DELETE a Value from XML based on Condition ?
Delete a element tag from the xml based on condition.
Xml format:
<Emps>
<Emp dept="HR">a</Emp>
<Emp dept="HR" />
<Emp dept="tech">R</Emp>
</Emps>
DECLARE @employeedata XML
DECLARE @deletecondition VARCHAR(20) = 'HR'
--xml
SELECT @employeedata = N'<Emps><Emp dept="HR">a</Emp><Emp dept="HR"></Emp><Emp dept="tech">R</Emp></Emps>'
SELECT @employeedata
--Deletion
SET @employeedata.modify('delete (//Emps/Emp[@dept=sql:variable("@deletecondition")])') -- delete all HR records
SET @employeedata.modify('delete (//Emps/Emp[@dept=sql:variable("@deletecondition")])[1]') -- delete first HR record
SELECT @employeedata
Output:
<Emps>
<Emp dept="tech">R</Emp>
</Emps>
56. How to find the existence of a value or tag in XML ?
Execute a two different code , based on occurrence of value in element
--Exist
IF @employeedata.exist('(/Emps/Emp[@dept="HR"])[1]') = 1
BEGIN
SELECT 'Employee Exist in HR dept'
END
ELSE
BEGIN
SELECT 'No employee exist in HR Dept'
END
No comments:
Post a Comment