SQL SERVER USER GUIDE

If you have any queries please leave a message here
Your Message
×


Reset "sa" Password in SQL Sever

  1. Login into SQL Server using Windows Authentication.

  2. In Object Explorer, expand Security folder, expand Logins folder. Right Click on "sa" account and go to Properties.

  3. Change "sa" password, and confirm it. Click OK.




Convert Table Data into XML File

A SELECT query returns results as a rowset. You can optionally retrieve formal results of a SQL query as XML by specifying the FOR XML clause in the query. The FOR XML clause can be used in top-level queries and in sub queries. The top-level FOR XML clause can be used only in the SELECT statement. In sub queries, FOR XML can be used in the INSERT, UPDATE, and DELETE statements.

In a FOR XML clause, you specify one of these modes :

Syntax
SELECT column_list FROM table_list WHERE filter_criteria FOR XML RAW | AUTO | EXPLICIT [, XMLDATA | , ELEMENTS | , BINARY BASE64 ]


Example
Consider the following table -
product(product_id, product_name, category_id)

RAW mode output

Query :
SELECT * FROM product
FOR XML AUTO;

Output :
<row product_id="10" product_name="Pierre Cardin" category_id="1" />
<row product_id="16" product_name="Classmate Notebook" category_id="2" />
<row product_id="17" product_name="Loknath Notebook" category_id="2" />


AUTO mode output

Query :
SELECT * FROM product
FOR XML AUTO;

Output :
<product product_id="10" product_name="Pierre Cardin" category_id="1" />
<product product_id="16" product_name="Classmate Notebook" category_id="2" />
<product product_id="17" product_name="Loknath Notebook" category_id="2" />


More Details on FOR XML




Import XML File into SQL Server

Consider the following table -
categories(category_id, category_name)
product(product_id, product_name, category_id)

Query :
SELECT * FROM product
FOR XML AUTO, ELEMENTS;

Output :
<product>
<product_id>10</product_id>
<product_name>Pierre Cardin</product_name>
<category_id>1</category_id>
</product>
<product>
<product_id>16</product_id>
<product_name>Classmate</product_name>
<category_id>2</category_id>
</product>
<product>
<product_id>25</product_id>
<product_name>Loknath</product_name>
<category_id>2</category_id>
</product>


XML File Restore Process :

--Setup a variable to take the file data
DECLARE @filedata XML

--Import the file contents into the variable
SELECT @filedata=BulkColumn FROM OPENROWSET(BULK 'C:\Users\Admin\Desktop\product.xml',SINGLE_BLOB) AS X

--Insert the xml data into our product table (product_id, product_name, Category_id)
INSERT INTO product(product_id, product_name, category_id)
SELECT
--"data" is our xml content alias
data.value('product_id[1]','int') AS product_id,
data.value('product_name[1]','varchar(20)') AS product_name,
data.value('category_id[1]','int') AS category_id

--This is the xpath to the individual records we want to extract
FROM @filedata.nodes('/product') AS X(data);



Backup categories and product Tables in one XML File

Query :
SELECT * FROM categories
INNER JOIN product ON product.category_id=categories.category_id
FOR XML AUTO, ELEMENTS;

Output :
<categories>
<category_id>1</category_id>
<category_name>Pen</category_name>
<product>
<product_id>10</product_id>
<product_name>Pierre Cardin</product_name>
<category_id>1</category_id>
</product>
</categories>
<categories>
<category_id>2</category_id>
<category_name>Notebook</category_name>
<product>
<product_id>16</product_id>
<product_name>Classmate</product_name>
<category_id>2</category_id>
</product>
<product>
<product_id>25</product_id>
<product_name>Loknath</product_name>
<category_id>2</category_id>
</product>
</categories>

Restore categories Table Data from XML File

--Setup a variable to take the file data
DECLARE @filedata XML

--Import the file contents into the variable
SELECT @filedata=BulkColumn FROM OPENROWSET(BULK 'C:\Users\Admin\Desktop\categories.xml',SINGLE_BLOB) AS X

--Insert the xml data into our categories table (category_id, category_name)
INSERT INTO categories(category_id, category_name)
SELECT

--"data" is our xml content alias
data.value('category_id[1]','int') AS category_id,
data.value('category_name[1]','varchar(20)') AS category_name

--This is the xpath to the individual records we want to extract
FROM @filedata.nodes('/categories') AS X(data);


Restore product Table Data from XML File

--Setup a variable to take the file data
DECLARE @filedata XML

--Import the file contents into the variable
SELECT @filedata=BulkColumn FROM OPENROWSET(BULK 'C:\Users\Admin\Desktop\categories.xml',SINGLE_BLOB) AS X

--Insert the xml data into our product table (product_id, product_name, category_id)
INSERT INTO product(product_id, product_name, category_id)
SELECT

--"data" is our xml content alias
data.value('product_id[1]','int') AS product_id,
data.value('product_name[1]','varchar(20)') AS product_name,
data.value('category_id[1]','int') AS category_id

--This is the xpath to the individual records we want to extract
FROM @filedata.nodes('/categories/product') AS X(data);



ABOUT US

QuestionSolves.com is an educational website that helps worldwide students in solving computer education related queries.

Also, different software like Visual Studio, SQL Server, Oracle etc. are available to download in different versions.

Moreover, QuestionSolves.com provides solutions to your questions and assignments also.


MORE TOPIC


Windows Command

UNIX Command

IGNOU Assignment Solution

IGNOU Question Paper Solution

Solutions of Different Questions


WHAT WE DO


Website Devlopment

Training

Home Learning

Provide BCA, MCA Projects

Provide Assignment & Question Paper Solution


CONTACT US


Follow Us