Note: - In this chapter we will first just skim through basic XML interview questions so that you do not get stuck up with simple questions.
What is XML?
XML (Extensible markup language) is all about describing data. Below is a XML which describes invoice data.
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.
What is the version information in XML?
“version” tag shows which version of XML is used.
What is ROOT element in XML?
In our XML sample given previously <invoice></invoice> tag is the root element. Root element is the top most element for a XML.
If XML does not have closing tag will it work?
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.
Is XML case sensitive?
Yes, they are case sensitive.
What’s the difference between XML and HTML?
XML describes data while HTML describes how the data should be displayed. So HTML is about displaying information while XML is about describing information.
Is XML meant to replace HTML?
No they both go together one is for describing data while other is for displaying data.
Can you explain why your project needed XML?
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.
What is DTD (Document Type definition)?
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.
What is well formed XML?
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.
What is a valid XML?
If XML is confirming to DTD rules then it’s a valid XML.
What is CDATA section in XML?
All data is normally parsed in XML but if you want to exclude some elements you will need to put those elements in CDATA.
What is CSS?
With CSS you can format a XML document.
What is XSL?
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.
What is Element and attributes in XML?
In the below example invoice is the element and the invnumber the attribute.
<invoice invnumber=1002></invoice>
Can we define a column as XML?
Yes, this is a new feature provided by SQL Server. You can define a column data type as XML for a table.
Figure 7.1 : - Specify XML data type
How do we specify the XML data type as typed or untyped?
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.
How can we create the XSD schema?
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:complexType>
</xs:element>
</xs:schema>'
After you have created the schema you see the MYXSD schema in the schema collections folder.
Figure 7.2 : - You can view the XSD in explorer of Management Studio
When you create the XML data type you can assign the MyXsd to the column.
Figure 7.3 : - MyXSD assigned to a column
How do I insert in to a table which has XSD schema attached to it?
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>1</
Orderid><CustomerName>Shiv</CustomerName></MyXSD>')
What is maximum size for XML datatype?
2 GB and is stored like varbinary.
What is Xquery?
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 interviewer is very sure that you do not know the real power of XML provided by SQL Server.
Figure 7.4 : - XML data
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 eq "4"]') = 1
Note: - It’s out of the scope of this book to discuss XQUERY. I hope and only hope guys many interviewers will not bang in this section. In case you have doubt visit www.w3c.org or SQL Server books online they have a lot of material in to this.
What are XML indexes?
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)
What are secondary XML indexes?
Secondary indexes are built on document attributes.
What is FOR XML in SQL Server?
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