SQL Server

XML In SQL Server

Introduction

  • XML stands for Extensible Markup Language.
  • XML was designed to store and transport data.
  • XML was designed to be both human and machine readable.

Use For XML in SQL Server

  • You can retrieve result as XML and then use the XML clause in the query.
  • The "FOR XML" uses top-level queries and sub-queries.
  • "FOR XML" is also used with a DML command.

There are four Models:

  1. Raw
  2. Explicit
  3. Path
  4. Auto

Description for Models

  • Raw

    It generates a single <row> element as per row in the rowset that is returned by a select statement. It also uses nested "FOR XML" queries.

  • Explicit

    You can mix attributes and elements in deciding the shape of XML. It requires a specific format for resulting format. This rowset format is then mapped into XML shape.

  • Path

    The nested "FOR XML" query capability provides the flexibility. These models are for the execution of the query for which they are set. They do not affect the results of any subsequent query.

  • Auto

    It generates a nested result, the Minimal control over the shape of XML data.

( Ref: MSDN).

All Models Examples

Firstly, create a table and insert data in the table

Step 1: Create a table.

You can create a table or use your existing table.

Create A Table
                                                             Figure 1.1

Step 2: Insert data in table.

Insert data of created table structure.

Insert Data of created Table
                                                               Figure 1.2

Step 3: Retrieve data in table.

You can see data is inserted from the table:

data is inserted form table
                                                            Figure 1.3

RAW

RAW
                                                                     Figure 1.4

Simple used RAW

Output

  1. <row EmpId="1" EmpName="Kamal" Address="IND" PhoneNo="9725469862" />  
  2. <row EmpId="2" EmpName="John" Address="USA" PhoneNo="9823654789" />  
  3. <row EmpId="3" EmpName="Jasen" Address="UK" PhoneNo="9236547891" />  
  4. <row EmpId="4" EmpName="Sachin" Address="RSA" PhoneNo="3245698751" />  
  5. <row EmpId="5" EmpName="Ricky" Address="AUS" PhoneNo="9875641231" />  
  6. <row EmpId="6" EmpName="Holder" Address="PAK" PhoneNo="9236547892" />  
  7. <row EmpId="7" EmpName="Rahul" Address="IND" PhoneNo="9365478934" />  
  8. <row EmpId="8" EmpName="Ram" Address="USA" PhoneNo="9236789545" />  
  9. <row EmpId="9" EmpName="Bond" Address="UK" PhoneNo="9123456789" />  
Elements
                                                                     Figure 1.5

Figure 1.5 - Used RAW with Elements.

Output
  1. <row>  
  2.     <EmpId>1</EmpId>  
  3.     <EmpName>Kamal</EmpName>  
  4.     <Address>IND</Address>  
  5.     <PhoneNo>9725469862</PhoneNo>  
  6. </row>  
  7. <row>  
  8.     <EmpId>2</EmpId>  
  9.     <EmpName>John</EmpName>  
  10.     <Address>USA</Address>  
  11.     <PhoneNo>9823654789</PhoneNo>  
  12. </row>  
  13. <row>  
  14.     <EmpId>3</EmpId>  
  15.     <EmpName>Jasen</EmpName>  
  16.     <Address>UK</Address>  
  17.     <PhoneNo>9236547891</PhoneNo>  
  18. </row>  
  19. <row>  
  20.     <EmpId>4</EmpId>  
  21.     <EmpName>Sachin</EmpName>  
  22.     <Address>RSA</Address>  
  23.     <PhoneNo>3245698751</PhoneNo>  
  24. </row>  
  25. <row>  
  26.     <EmpId>5</EmpId>  
  27.     <EmpName>Ricky</EmpName>  
  28.     <Address>AUS</Address>  
  29.     <PhoneNo>9875641231</PhoneNo>  
  30. </row>  
  31. <row>  
  32.     <EmpId>6</EmpId>  
  33.     <EmpName>Holder</EmpName>  
  34.     <Address>PAK</Address>  
  35.     <PhoneNo>9236547892</PhoneNo>  
  36. </row>  
  37. <row>  
  38.     <EmpId>7</EmpId>  
  39.     <EmpName>Rahul</EmpName>  
  40.     <Address>IND</Address>  
  41.     <PhoneNo>9365478934</PhoneNo>  
  42. </row>  
  43. <row>  
  44.     <EmpId>8</EmpId>  
  45.     <EmpName>Ram</EmpName>  
  46.     <Address>USA</Address>  
  47.     <PhoneNo>9236789545</PhoneNo>  
  48. </row>  
  49. <row>  
  50.     <EmpId>9</EmpId>  
  51.     <EmpName>Bond</EmpName>  
  52.     <Address>UK</Address>  
  53.     <PhoneNo>9123456789</PhoneNo>  
  54. </row>  
EXPLICIT

Explicit
                                                                        Figure 1.6

Figure 1.6 - Used Explicit Model

Output
  1. <Emp>  
  2.     <Emp EmpID="1">  
  3.         <EMPName>Kamal</EMPName>  
  4.         <Address>IND</Address> </Emp>  
  5.     <Emp EmpID="2">  
  6.         <EMPName>John</EMPName>  
  7.         <Address>USA</Address> </Emp>  
  8.     <Emp EmpID="3">  
  9.         <EMPName>Jasen</EMPName>  
  10.         <Address>UK</Address> </Emp>  
  11.     <Emp EmpID="4">  
  12.         <EMPName>Sachin</EMPName>  
  13.         <Address>RSA</Address> </Emp>  
  14.     <Emp EmpID="5">  
  15.         <EMPName>Ricky</EMPName>  
  16.         <Address>AUS</Address> </Emp>  
  17.     <Emp EmpID="6">  
  18.         <EMPName>Holder</EMPName>  
  19.         <Address>PAK</Address> </Emp>  
  20.     <Emp EmpID="7">  
  21.         <EMPName>Rahul</EMPName>  
  22.         <Address>IND</Address> </Emp>  
  23.     <Emp EmpID="8">  
  24.         <EMPName>Ram</EMPName>  
  25.         <Address>USA</Address> </Emp>  
  26.     <Emp EmpID="9">  
  27.         <EMPName>Bond</EMPName>  
  28.         <Address>UK</Address> </Emp>  
  29. </Emp>  
PATH

PATH
                                                                  Figure 1.7

Output
  1. <row>  
  2.     <EmpId>1</EmpId>  
  3.     <EmpName>Kamal</EmpName>  
  4.     <Address>IND</Address>  
  5.     <PhoneNo>9725469862</PhoneNo>  
  6. </row>  
  7. <row>  
  8.     <EmpId>2</EmpId>  
  9.     <EmpName>John</EmpName>  
  10.     <Address>USA</Address>  
  11.     <PhoneNo>9823654789</PhoneNo>  
  12. </row>  
  13. <row>  
  14.     <EmpId>3</EmpId>  
  15.     <EmpName>Jasen</EmpName>  
  16.     <Address>UK</Address>  
  17.     <PhoneNo>9236547891</PhoneNo>  
  18. </row>  
  19. <row>  
  20.     <EmpId>4</EmpId>  
  21.     <EmpName>Sachin</EmpName>  
  22.     <Address>RSA</Address>  
  23.     <PhoneNo>3245698751</PhoneNo>  
  24. </row>  
  25. <row>  
  26.     <EmpId>5</EmpId>  
  27.     <EmpName>Ricky</EmpName>  
  28.     <Address>AUS</Address>  
  29.     <PhoneNo>9875641231</PhoneNo>  
  30. </row>  
  31. <row>  
  32.     <EmpId>6</EmpId>  
  33.     <EmpName>Holder</EmpName>  
  34.     <Address>PAK</Address>  
  35.     <PhoneNo>9236547892</PhoneNo>  
  36. </row>  
  37. <row>  
  38.     <EmpId>7</EmpId>  
  39.     <EmpName>Rahul</EmpName>  
  40.     <Address>IND</Address>  
  41.     <PhoneNo>9365478934</PhoneNo>  
  42. </row>  
  43. <row>  
  44.     <EmpId>8</EmpId>  
  45.     <EmpName>Ram</EmpName>  
  46.     <Address>USA</Address>  
  47.     <PhoneNo>9236789545</PhoneNo>  
  48. </row>  
  49. <row>  
  50.     <EmpId>9</EmpId>  
  51.     <EmpName>Bond</EmpName>  
  52.     <Address>UK</Address>  
  53.     <PhoneNo>9123456789</PhoneNo>  
  54. </row>  
Query
                                                               Figure 1.8

Figure 1.8 - Another way to use path.

used path
                                                      Figure 1.9

Figure 1.9 - Example: used path as root element.

Auto

root element
                                                                  Figure 1.10

Figure 1.10 - Used as AUTO models.

Output
  1. <EmpMst EmpId="1" EmpName="Kamal" Address="IND" PhoneNo="9725469862" />  
  2. <EmpMst EmpId="2" EmpName="John" Address="USA" PhoneNo="9823654789" />  
  3. <EmpMst EmpId="3" EmpName="Jasen" Address="UK" PhoneNo="9236547891" />  
  4. <EmpMst EmpId="4" EmpName="Sachin" Address="RSA" PhoneNo="3245698751" />  
  5. <EmpMst EmpId="5" EmpName="Ricky" Address="AUS" PhoneNo="9875641231" />  
  6. <EmpMst EmpId="6" EmpName="Holder" Address="PAK" PhoneNo="9236547892" />  
  7. <EmpMst EmpId="7" EmpName="Rahul" Address="IND" PhoneNo="9365478934" />  
  8. <EmpMst EmpId="8" EmpName="Ram" Address="USA" PhoneNo="9236789545" />  
  9. <EmpMst EmpId="9" EmpName="Bond" Address="UK" PhoneNo="9123456789" />  
Code
                                                                  Figure 1.11

Note: Please find the attachment for code.

Up Next
    Ebook Download
    View all
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs