xml interview questions – SQL Server Interview Questions on XML Integration

by

xml interview questions: We have compiled most frequently asked SQL Server Interview Questions which will help you with different expertise levels.

SQL Server Interview Questions on XML Integration

Note  In this chapter we willfirst just skim through basic XML interview questions so that you 
         do not get stuck up with simple questions.

Question 1.
What is XML?
Answer:
XML (Extensible markup language) is all about describing data. Below is a XML which describes invoice data.

<?xml version="1.0 " encodings"ISO-8859-1"?>
<invoice>
<productname>Shoes</productname>
<qty>12</qty>
<totalcost>100</totalcost>
<discount>10</discount>
</invoice>

An XML tag is not something predefined but it is something you have to define according to your needs. For instance in the above example of invoice all tags are defined according to business needs. The XML document is self explanatory, any one can easily understand looking at the XML data what exactly it means.

Question 2.
What is the version information in XML?
Answer:
“version” tag shows which version of XML is used.

Question 3.
What is ROOT element in XML?
Answer:
In our XML sample given previously <invoicex/invoice> tag is the root element. Root element is the top most element for a XML. .

Question 4.
If XML does not have closing tag will it work?
Answer:
No, every tag in XML which is opened should have a closing tag. For instance in the top if I remove </discount> tag that XML will not be understood by lot of application.

Question 5.
Is XML case sensitive?
Answer:
Yes, they are case sensitive.

Question 6.
What’s the difference between XML and HTML?
Answer:
XML describes data while HTML describes how the data should be displayed. So HTML is about displaying information while XML is about describing information.

Question 7.
Is XML meant to replace HTML?
Answer:
No they both go together one is for describing data while other is for displaying data.

Question 8.
Can you explain why your project needed XML?
Answer:

Note  This is an interview question where the interviewer wants to know why you have chosen XML.

Remember XML was meant to exchange data between two entities as you can define your user friendly tags with ease. In real world scenarios XML is meant to exchange data. For instance you have two applications who want to exchange information. But because they work in two complete opposite technologies it’s difficult to do it technically. For instance one application is made in JAVA and the other in .NET. But both languages understand XML so one of the applications will spit XML file which will be consumed and parsed by other application.s
You can give a scenario of two applications which are working separately and how you chose XML as the data transport medium.

Question 9.
What is DTt> (Document Type definition)?
Answer:
It defines how your XML should structure. For instance in the above XML we want to make it compulsory to provide “qty” and “totalcost”, also that these two elements can only contain numeric. So you can define the DTD document and use that DTD document with in that XML.

Question 10.
What is well formed XML?
Answer:
If a XML document is confirming to XML rules (all tags started are closed, there is a root element etc) then it’s a well formed XML.

Question 11.
What is a valid XML?
Answer:
If XML is confirming to DTD rules then it’s a valid XML.

Question 12.
What is CDATA section in XML?
Answer:
All data is normally parsed in XML but if you want to exclude some elements you will need to put those elements in CDATA,

Question 13.
What is CSS?
Answer:
With CSS you can format a XML document.

Question 14.
What is XSL?
Answer:
XSL (the extensible Stylesheet Language) is used to transform XML document to some other document. So its transformation document which can convert XML to some other document. For instance you can apply XSL to XML and convert it to HTML document or probably CSV files.

Question 15.
What is Element and attributes in XML?
Answer:
In the below example invoice is the element and the invnumber the attribute.
<invoice invnumber=1002></invoice>

Question 16.
Can we define a column as XML?
Answer:
Yes, this is a new feature provided by SQL Server. You can define a column data type as XML for a table.

SQL Server Interview Questions on XML Integration chapter 7 img 1

Question 17.
How do we specify the XML data type as typed or untyped?
Answer:
If there is a XSD schema specified to the data type then it’s typed or else it’s untyped. If you specify XSD then with every insert SQL Server will try to validate and see that is the data adhering to XSD specification of the data type.

Question 18.
How can we create the XSD schema?
Answer:
Below is the DDL statement for creating XML schema.

CREATE XML SCHEMA COLLECTION MyXSD AS
N'<?xml version="1. 0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
 elementFormDefault="qualified" targetNamespace="http://MyXSD">
  <xs:element name="MyXSD">
    <xs:complexType>
     <xs:sequence>
      <xs:element name="Orderid" type="xs:string" />
      <xs:element name="CustomerName" type="xs:string" />
    </xs:sequence>
  </xs:complexType>
 </xs:element>
</xs:schema>

After you have created the schema you see the MYXSD schema in the schema collections folder.

SQL Server Interview Questions on XML Integration chapter 7 img 2

When you create the XML data type you can assign the MyXsd to the column.

SQL Server Interview Questions on XML Integration chapter 7 img 3

Question 19.
How do I insert in to a table which has XSD schema attached to it?
Answer:
I know many developers will just say what the problem with simple insert statement. Well guys its not easy with attaching the XSD its now a well formed datatype.The above table I have named as xmltable. So we had specified in the schema two nodes one is ordered and the other customername. So here’s the insert.
Insert into xmltable values ( ‘ <MyXSD xmlns=’’http: //MyXSD” ><Orderid>l</ Orderid><CustomerName>Shiv</CustomerNamex/MyXSD> ’)

Question 20.
What is maximum size for XML datatype?
Answer:
2 GB and is stored like varbinary.

Question 21.
What is Xquery?
Answer:
In a typical XML table below is the type of data which is seen. Now I want to retrieve orderid “4”. I know many will jump up with saying use the “LIKE” keyword. Ok you say that an interviewer is very sure that you do not know the real power of XML provided by SQL Server.

SQL Server Interview Questions on XML Integration chapter 7 img 4

Well first thing XQUERY is not that something Microsoft invented, it’s a language defined by W3C to query and manipulate data in a XML. For instance in the above scenario we can use XQUERY and drill down to specific element in XML.
So to drill down here’s the XQUERY

SELECT * FROM xmltable .
WHERE TestXml.exist('declare namespace
xd=http://MyXSD/xd:MyXSD[xd:Orderid eg "4"]’) = 1
Note  It's out of the scope of this book to discuss XQUER Y. I hope and only hope guys many interviewers 
         will not bang in this section. In case you have doubt visit wwzv. w3c.org or SQL Server books online they 
         have a lot of material in to this.

Question 22.
What are XML indexes?
Answer:
XML data types have huge size 2 GB. But first thing is that you should have a primary key on the XML data type column. Then you can use the below SQL statement to create index on the XML column:-

CREATE PRIMARY XML INDEX xmlindex ON xmltable(TestXML)

Question 23.
What are secondary XML indexes?
Answer:
Secondary indexes are built on document attributes.

Question 24.
What is FOR XML in SQL Server?
Answer:
FOR XML clause returns data in XML rather than simple rows and columns. For instance if you fire the below query on any table you will get XML output:-
SELECT * FROM MyTable FOR XML AUTO

Question 25.
Can I use FOR XML to generate SCHEMA of a table and how?
Answer:
The below SQL syntax will return the SCHEMA of the table.

SELECT * FROM MyTable FOR XML AUTO, XMLSCHEMA

Question 26.
What is the OPENXML statement in SQL Server?
Answer:
We had seen that FOR XML returns a XML format of a table data. And open XML does the vice versa of it. If you pass XML document to it will convert it to rows and columns.

Question 27.
I have a huge XML file which we want to load in database?
Answer:
Twist: – Can I do a BULK load of XML in database?
Below is the SQL statement which will insert from “MyXml.xml” in to “MyTable”.

INSERT into MyTable(MyXMlColumn) SELECT * FROM OPENROWSET (Bulk 'c:\MyXml.xml', SINGLE_CLOB) as abc

Question 28.
How to call stored procedure using HTTP SOAP?
Answer:
Twist: – Can I create web services for SQL Server objects?

Note  Ok every one reading this answer out of dedication I have switched off my mobile and lam writing this answer.

You can call a stored procedure using HTTP SOAP. This can be done by creating END POINTS using the “CREATE ENDPOINT” DDL statement. I have created a TotalSalesHttpEndPoint which can be called later through “webservices”.

CREATE ENDPOINT TotalSalesHttpEndPoint
STATE = STARTED
AS HTTP(
   PATH = '/sql ',
   AUTHENTICATION = (INTEGRATED ),
   PORTS = ( CLEAR ),
   SITE = 'server'
   )
FOR SOAP (
    WEBMETHOD 'http://tempUri.org/'. 'GetTotalSalesOfProduct’
              (name='AdventureWorks.dbo.GetTotalSalesOfProduct ’, schema=STANDARD ),
    BATCHES = ENABLED,
    WSDL = DEFAULT,
    DATABASE = 'AdventureWorks ',
    NAMESPACE = 'http://AdventureWorks/TotalSales'
    )

Question 29.
What is XMLA?
Answer:
XMLA stand for XML for Analysis Services. Analysis service is covered in depth in data mining and data ware housing chapters. Using XMLA we can expose the Analysis service data to the external world in XML. So that any data source can consume it as XML is universally known.