XML Concepts Flashcards
What does XML stand for?
XML stands for Extensible Markup Language.
XML is known as self-describing data because you get the data plus metadata.
XML capabilities allow it to serve much like a self-describing relational database stored as a file. All data-aware applications can interact with XML.
What are the two components of XML?
XML is comprised of two components.
- Data
- Metadata - The description of the data, including relationships and properties of the data.
What are XML tags?
XML utilizes tags < > to create the data ‘outline’. The tags can also be nested.
The tag has two sections.
- Beginning tags
The example of Beginning tag would be , It sits between brackets and contains no slash.
Beginning tag would be < Name >
- Ending tags
The example of ending tag would be . It appears identical to beginning tag, except it contains a forward slash. Ending tags must follow the same sequence as the beginning tags.
Ending tag would be < / Name >
Please note: If a tag contains no element data, or element children, then the beginning and ending tag may be combined into one tag. for example < Name / >
is all that is required if there is no element in the Name tag.
What are XML elements?
Elements are actually the tags themselves. Element data is the data which sits between tags. For example, Seahawks is element data. It is data belonging to the element , Team.
< Team > Seahawks < / Team >
What are Root Elements and Top Level Elements in XML?
The top Tag in the XML is the Root Element or the Root Node. All data in the XML appears beneath the Root Element.
The elements falling just under the Root Element are the Top Level Element.
What are XML Attibutes?
XML element tags may contain information within the tags providing descriptive information about the element or its properties. These are known as Attributes.
Attribute data appears with the opening tag name and is enclosed in quotation marks.
For example
< Team Name=”Seahawks” >< / Team >
What is XML?
- Metadata is data about your data. Metadata is information which helps to describe the properties and relationships of data.
- XML is self-describing data, as it contains both data and metadata.
- XML holds its data adn metadata in a hierarchical set of tags.
- A beginning and ending tag always have the same name. You can tell them apart because the ending tag starts with a forward slash / .
- The ending tag for < Jerrin > would be < / Jerrin >
- The data in XML can be in the form of attributes or elements.
- Element data is stored between the beginning and ending element tag.
- Attribute data is stored inside of the beginning tag.
- XML is case sensitive.
- XML tag names cannot have spaces between them.
- Each element or attribute has exactly one parent (Except for the root element).
- Any node which is two or more levels above is called as Ancestor.
- Any node that is two or more levels below is called Descendant.
- An attribute is data that is inside an element.
What are streams and what are the different ways you can stream XML data from SQL Server?
In SQL Server terms , a Stream resembles a table. The grid like presentation of rows and columns that you get when you run a query. This is because SQL Server prefers to present data in a grid shape. However, XML does not set things up in rows and columns.
XML Streams
XML actually has many modes of streaming output. you can instruct SQL Server to stream your XML result in the mode you prefer.
Following are the various modes of streaming output in XML
- XML Raw Mode
- Output with ROOT Element option
- output with Elements option.
- XSINIL
What is XML Raw Mode?
To our base query, we need to add the keywords “FOR XML” in order for our results to apprear as XML. We also need to specify the mode, which in this case is RAW
The example query would be
SELECT LocationID, City
FROM Location
FOR XML RAW
Raw is the easiest type of XML to run. The output in the screenshot attached is the result in XML raw dataformat. Notice the result is a hyperlink, which you click to view your results. The second screenshot shows the XML after clicking the hyperlink.
Notice that the default tag label is “row” for each row of our result set. The data appear as attributes.
XML will allow you to override the default name < row > and set it to something mode descriptive.
Raw is the simplest mode where all data is considered to be at the same level with no nesting. The XML RAW command by default does not create a root.
How do you give a descriptive custom tag for the output in XML RAW mode?
XML will allow you to override the default name < row > and set it to soemthing more descriptive. The syntax shown here will change the name for each attribute tag to “RowLocation”
SELECT LocationID, City
From Location
FOR XML RAW(“rowLocation”)
You can see the output in the screenshot attached. Observ the red Intellisense underline and mouseover tag appearing in the result. XML document cannot contain multiple root level elements. Also notice we have four top-level nodes and no root node. SQL Server would prefer that we have a root element appreaing in our data.
How do you generate XML output with the Root Element Option?
XML is considered complete(known as well-formed XML) only if it has a root tag which emcompasses all other tags. Adding the keywork ROOT to our code specifies the ROOT element option.
Adding the ROOT command to our code will add root tag in our XML data.
Select LocationID, City
From Location
FOR XML RAW(‘Location’), ROOT
in the screenshot you can see that instead of just XML fragment, our code produces an entire XML stream. This output is a well-formed XML document.
By default the ROOT command would add a root element with the tag “root”. If you want to add a custom name for the Root element then you can do the following.
Select LocationID, City
From Location
FOR XML RAW(‘Location’), ROOT(‘Jerrin’)
In the output you can see that the Root element is now named “Jerrin”. Notice that the data appears as attributes inside the tag.
How do you generate an XML output with a custom name for the Root element?
By default the ROOT command would add a root element with the tag “root”. If you want to add a custom name for the Root element then you can do the following.
Select LocationID, City
From Location
FOR XML RAW(‘Location’), ROOT(‘Jerrin’)
In the output you can see that the Root element is now named “Jerrin”. Notice that the data appears as attributes inside the tag.
How do you generate XML output using the ELEMENTS option?
The raw mode likes to store all data as attributes. You can choose whether data appears as attributes or as elements. If you prefer elements, then you can append the ELEMENTS option to the XML RAW mode.
Select LocationID, City
From Location
FOR XML RAW(‘Location’), ROOT(‘Jerrin’),ELEMENTS
Looking closely at the XML output in the screenshot you’ll recognize the values for records 1,2,3 and 4 from the location table.
PLEASE NOTE: With the ELEMENTS option if the row has any NULL value then it is not shown as an Element in the XML output. This is a default behaviour. To include the NULL values in the XML output for the elements we would have to use the XSINIL option.
What is the XSINIL option?
The XSINIL option allows you to force a tag(s) to be present, even if the underlying data is null.
For fields in SQL Server which include a null value for some records but are populated with regular values in the other records, you will seem to have missing tags for the NULL record. Often this is alright, as missing tags are presumed to be null. However, if you require all tags to be present(even if they have no data), then you can specify the XSINIL option for your XML stream.
The output of the following query is shown in the screenshot.
select FirstName,LastName,LocationID
FROM Employee
FOR XML RAW(‘Employee’),ROOT(‘Emp’),ELEMENTS XSINIL
In the output we see that the third sub-element for John Marshbank. The location tag is no longer missing. It is present and show the value of xsi:nil=”true” in place of a LocationID
Your Root tag is named < ThisIsATag > in your well formed XML. How should the ending root tag be named?
< / ThisIsATag >
What do you call data inside of a tag like the example seen below?
< Team Name=’Empyreans” >
Attribute
Raw is the only XML Mode?
True or False
False
Raw automatically adds the root element tag in SQL Server.
True or False
False
Give some details about XML AUTO mode?
When selecting from a single table, the Auto Mode resembles the Raw mode with one exception. Whereas the Raw mode defaults each tag to < row >, the Auto Mode names each tag to the table name (or table alias) listed in the FROM clause of the query.
The code for Auto Mode is similar to the code for RAW.
Select *
FROM Location
FOR XML AUTO
The resulting XML is also quite similar to RAW mode. The data appear as attributes, and by default there is no root element. However, note that the Auto Mode has defaulted the row tags to the table name as it appears in the FROM clause. The tag < Location > is named for the Location table.
If we specify “Location” in our XML Raw mode, then the stream resulting from our Raw query would be identical to our Auto query.
How does XML AUTO mode works with multiple table queries?
Also
If AUTO mode were to be used and it names the tag after the table what do queries with 2 table (or more) names get?
Also
What is the difference between the XML RAW and XML AUTO modes?
We will look at the following query for this example.
SELECT City,FirstNAme,LastName
FROM Location inner join Employee
on Location.LocationID=Employee.LocationID
FOR XML AUTO
The answer is both table names would be there in the XML output. The Auto mode generates a tag for each table: the Location data attributes (City) is contained in the < Location > tag, and the Employee data attributes (FirstName, LastName) are contained in the < Employee > tag.
So this demonstrates a difference between the Auto mode and the Raw mode. The Auto mode will nest the results, but the RAW mode will not.
Look back to the query and note that the Auto Mode placed tags in the order in which the tables appreared in our query. In this example, the Location table becomes the top-level element tag because the first field in the SELECT list is City whcih is from the Location Table. The second (nested ) table is Employee which appreas beneath as a child element.
If you were to list any field from the Employee table first, rather than a field from the Location table, then the < Employee > element would have the < Location > element nested inside of it.
For example if you write the following query you would get the result as shown in the screenshot for query 2 output.
SELECT FirstName, City, LastName
FROM Location INNER JOIN Employee
on Location.LocationID=Employee.LocationID
FOR XML AUTO
How do you use ROOT with AUTO ?
Using Root keyword in combination with the Auto mode produces the same result as it does with the RAW mode, your XML stream will contain a root (named < root > by default). To specify a name for te root, put this name in the parenthesis inside single quotes right after the ROOT keyword in your code.
Lets see the output of the following query
SELECT City,FirstName, LastName
FROM Location INNER JOIN Employee
on Location.LocationID=Employee.LocationID
FOR XML AUTO, ROOT
Our XML result appears the same as in the prior result, except that now all of our data is enclosed within the opening and closing tags named < root >.
Similarly the following query will replace the default root tag of < root > with < Jerrin >
SELECT City,FirstName, LastName
FROM Location INNER JOIN Employee
on Location.LocationID=Employee.LocationID
FOR XML AUTO, ROOT(‘Jerrin’)
What is XML PATH mode?
How is XML PATH different from XML RAW AND AUTO?
The XML Raw and Auto modes are great for displaying data as all attributes or all elements - but not both at once. If you want your XML stream to have some of its data shown in attributes and some shown as elements, then you can use the XML Path mode.
Let us see the example of the following query
Query 1
- *SELECT** *
- *FROM** location AS loc INNER JOIN employee AS emp
- *ON** loc.LocationID = emp.LocationID
- *FOR XML PATH**
Much like Raw and Auto, each top-level element has a tag named “row” and by default there is no element. However, unlike the previous modes we’ve seen, the Path mode defaults to putting all its data in elements.
1Now we’ll itemize the fields in our query.
Query 2
- *SELECT** City, FirstName, LastName
- *FROM** location AS loc INNER JOIN employee AS emp
- *ON** loc.LocationID = emp.LocationID
- *FOR XML PATH**
The result appears much the same. We see “row” as the default name of the opening and closing tags. We also see nested within each “row”, elements for each of the three fields we itemized in our query (City, FirstName, LastName).
you can modify the query so that the XML result will include an < Employee > root tag. You can do this by adding the ROOT (‘Employee’) to the query like shown below
- *SELECT** City, FirstName, LastName
- *FROM** location AS loc INNER JOIN employee AS emp
- *ON** loc.LocationID = emp.LocationID
- *FOR XML PATH, ROOT (‘Employee’)**
What are custom attributes for XML Stream?
If you are using an XML Path stream, then by default all of the values will be shown as elements. However, you can pick one or more of your elements to instead be shown as an attribute(s). Use the [@Fieldname] syntax to do this.
Consider the following query
Query 1
- *SELECT** City AS [@CityName], FirstName, LastName
- *FROM** location AS loc INNER JOIN employee AS emp
- *ON** loc.LocationID = emp.LocationID
- *FOR XML PATH(‘Employee’), ROOT(‘Employees’)**
In the query above we use the alias the city field as [@City]. One easy way to think of this code syntax is to remember that @ (a.k.a. , the “at” sign) goes with “Attributes”.
In the output we can see that City is now an attribute of < Employee > instead of a seperate element.
How do you create custom elements in the XML output?
The element’s name does not need to be named after the field from the table. You can set it to anything you want by specifying a field alias.
So all we need to do here is alias the fields using the AS [ElementName] syntax.
consider the following query
Query 1
SELECT City AS [@CityName], FirstName AS [First],
LastName AS [Last]
FROM location AS loc INNER JOIN employee AS emp
ON loc.LocationID = emp.LocationID
FOR XML PATH(‘Employee’), ROOT(‘Employees’)
In the query output we can see that
Data previously shown as attributes are still attributes, and all the elements are still elements. The only change is that the original element names (FirstName and LastName) have changed (to First and Last, respectively).
How do you do custom level nesting in XML output?
The Path mode also nests elements within other elements. The nesting order is based on the order of fields in your SELECT list.
You can specify a custom level by adding a / (forward slash) before each element that you want nested immediately below the new level. The name of the custom level precedes the forward slash.
Consider the following query for example
Query 1
SELECT City AS [@CityName],FirstName AS [Name/First],
LastName AS [Name/Last]
FROM location AS loc INNER JOIN employee AS emp
ON loc.LocationID = emp.LocationID
FOR XML PATH(‘Employee’), ROOT(‘Employees’)
the query output will show a new child-level element < Name > which apprears beneath < Employee > and includes our aliased elements , < First > and < Last >.
How do you generate XML Nameless fields?
A nameless field belongs to no tag in your XML file. This can be useful for manipulating your XML stream for readability.
The following query will create a Nameless field in the XML output.
- *SELECT** EmpID, FirstName AS [*]
- *FROM** Employee
- *FOR XML PATH(‘Employee’), ROOT**
What is Shredding XML? What are the different ways you can do it?
Operations which parse and consume XML data are known collectively by the term shredding XML.
If you want to take XML data and create a result set in SQL Server, you must first store the XML in memory. The process of preparing XML in SQL includes storing the XML in memory and processing the XML so that all the data and metadata is ready and available for you to query.
_Using sp_XML_PrepareDocument_
When you send an XML document into memory, SQL Server gives you a number (called a handle) which you need later when referring to that document. In Figure 3.14, we will see the “claim ticket” created: we will send our XML document into memory and in exchange we will get back the handle in the form of an integer.
To send our document into memory, we first need to declare an XML variable. In order for this variable to contain our XML document, we will set it equal to our XML
Next we will declare the variable @hDoc, which we know will be an integer because it is the variable which will act as our document handle (i.e., our “claim ticket”). We will also use sp_XML_PrepareDocument, a system-supplied stored procedure which reads our XML document (@Doc), parses it, and makes it available for SQL’s use (Figure 3.13).
When we send our XML to SQL Server’s internal cache, we will receive a number which functions as our “claim ticket”. Run all of the code together, including a SELECT statement to display the document handle (i.e., our “claim ticket” which SQL Server provides in exchange for the XML document) (Figure 3.14).
Be sure to run this code only once, otherwise you will create multiple handles and instances of your XML document.
Our complete query for preparing the XML in memory would be as follows
DECLARE @Doc XML=
‘< cust CustomerID=”2” CustomerType=”Consumer” >
< Order InvoiceID=”943” OrderDate=”2008-02-07T02:45:03.843” >
< OrderDetail ProductID=”72” Quantity=”4” / >
< / Order >
< / cust >‘
DECLARE @hDoc int
Exec sp_xml_prepareDocument @hDoc OUTPUT, @Doc
select @hDoc
Using the OPENXML Function
We just sent our prepared XML into the SQL Server’s internal cache so that we may pull out the data we want. The OPENXML function provides a rowset view of your XML data. It works with the in-memory copy of the XML document you’ve stored and provides a view of the data, no longer formatted as XML but with all its parts separated into a large grid. This allows you to query just the data that you need.
Rowpattern
We know the key to accessing the stored XML document is the document handle or “claim ticket”. The first argument needed by the OPENXML function is this value expressed as an integer. The second argument is the rowpattern hint for the data we wish to see. After declaring an integer variable and setting it equal to 1 (i.e., the value of our document handle, see Figure 3.14), we can use a SELECT statement to query the result set of the OPENXML function. The variable @iNum is the first parameter. The second parameter ‘/cust/Order/OrderDetail’ specifies that we wish to see data for the OrderDetail element level (Figure 3.16).
DECLARE @iNum INT
SET @iNum = 1
SELECT *
FROM OPENXML(@iNum,’/cust/Order/OrderDetail’)
Since XML can have root tags, top-level tags, and many levels of child tags, rowpatterns are needed to figure out which level represents your row data. A rowpattern is an XPath pattern telling your query where to look for the data that you want to see in your result. In our current example, the rowpattern hint (‘/cust/Order/OrderDetail’) narrows our query to the attributes found at the OrderDetail element level (Figure 3.16). While the surrounding data isn’t immediately interpretable, we can see the text for the ProductID attribute shows a 72, and the text for the Quantity attribute shows a 4.
Shredding from the Rowpattern Level
We want to pull at least one of these columns with the OPENXML function. You might remember that all of our data is at many levels in our XML and the Rowpattern specified the data is at the /cust/Order/OrderDetail level. That level will get our ProductID and Quantity fields as seen in the XML stream below:
< cust CustomerID=”2” CustomerType=”Consumer” >
< Order InvoiceID=”943” OrderDate=”2008-02-07” >
< OrderDetail ProductID=”72” Quantity=”4” / >
< / Order >
< / cust >
Adding the WITH clause to our existing query allows us to pick just the values we wish to see. Our query specifies that we are still interested in data from the OrderDetail element level (Figure 3.17). Our WITH clause lists the field names we want (ProductID, Quantity) from this element level and that these values should be expressed as integer data. In other words, ProductID and Quantity are both integers.
DECLARE @iNum INT
SET @iNum = 1
SELECT *
FROM OPENXML(@iNum,’/cust/Order/OrderDetail’)
WITH (ProductID INT, Quantity INT)
Please note: In the above query you can also alias the column names. for example instead of ProductID you could alias it as ProdID. The example query would be as follows
DECLARE @iNum INT
SET @iNum = 1
SELECT *
FROM OPENXML(@iNum,’/cust/Order/OrderDetail’)
WITH (ProdID INT ‘@ProductID’, Quantity INT)
In this we have used @ProductID to refer to the element name in the XML.
What is Rowpatten Recursion in the OPENXML option?
It’s common to see elements inside of elements with the same name. Sometimes the name of the element is more important than its exact level. In other words, you could see one employee level nested beneath another employee level, and so forth. In our example, we will see how level recursion can help us locate the data we need within XML tags having the same name.
Let’s create a simple XML document showing a small organization having one boss, Tom. Tom will have two employees, Dick and Harry.
XML as shown below
< Root >
< Emp User = “Tom” >
< Emp User = “Dick” / >
< Emp User = “Harry” / >
< / Emp >
< / Root >
The details about the XML are in the screenshot attached.
Let us see the following code to extract data from the above XML.
DECLARE @hDoc INT
DECLARE @Doc XML SET @Doc =
‘< Root>
< Emp User = “Tom” >
< Emp User = “Dick” / >
< Emp User = “Harry” / >
< / Emp >
< / Root >’
EXEC sp_XML_PrepareDocument @hDoc OUTPUT, @Doc
- *SELECT** *
- *FROM OPENXML(@hDoc ,‘/Root/Emp’**)
- *WITH** (EmployeeName VARCHAR(max) ‘@User’)
The output of the above select would be TOM. Our Rowpattern parameter (‘Root/Emp’) specifies that we want only the top-level element.
If we modify the above select to something like this
- *SELECT** *
- *FROM OPENXML(@hDoc ,‘/Root/Emp/Emp’**)
- *WITH** (EmployeeName VARCHAR(max) ‘@User’)
In the above code the child-level element ( rowpattern ‘/Root/Emp/Emp’) contains Dick and Harry.
A slight modification to our rowpattern parameter specifies that we want to pull data from every level, at or beneath the , and having an XML tag named (code shown below). This is an example of using rowpattern’s relative navigation capability. Thus far our row level specifications have been absolute - our OPENXML queries have explicitly named each level and instructed SQL Server to pull data from that level. In our next section, we will see some of the tools SQL Server makes available for relative navigation of our XML data.
- *DECLARE** @iDoc INT
- *SET** @iDoc=1
- *SELECT** *
- *FROM OPENXML(@iDoc,‘/Root//Emp’**)
- *WITH** (EmployeeName VARCHAR(max) ‘@User’)
What are the optional parameters of OPENXML?
We are now familiar with the two required parameters of the OPENXML table-valued function:
- The document handle which identifies the in-memory location of the XML document, and
- The rowpattern.
There is one more optional parameter which can make your life easier when your data is stored in a mixture of attributes and elements.
OPENXML FLAG(Optional)
The third parameter of the OPENXML function is the flag. The flag is optional - if you do not specify a flag, your query will search only for values mapped as attributes. Key values for this flag are 1, 2, or 3:
1 = searches for attributes (if no flag specified, your query will bring in the values which are mapped as attributes)
2 = searches for elements
3 = searches for attributes and elements
Consider the following code
DECLARE @Doc XMLSET @Doc =
‘< SalesInvoice InvoiceID=”1000” CustomerID=”123”
OrderDate=”2004-03-07” >
< Items >
< Item ProductCode=”12” Quantity=”2”
UnitPrice=”12.99” > < ProductName > Bike < / ProductName >
< / Item >
< Item ProductCode=”41” Quantity=”1” UnitPrice=”17.45” >
< ProductName >Helmet< / ProductName >
< / Item >
< Item ProductCode=”2” Quantity=”1” UnitPrice=”2.99” >
< ProductName > Water Bottle < / ProductName >
< / Item >
< / Items >
< / SalesInvoice >’
DECLARE @HandleDoc INT EXEC sp_XML_PrepareDocument @HandleDoc OUTPUT, @Doc
DECLARE @iNum INT
SET @iNum = 1 SELECT * FROM
OPENXML (@iNum, ‘SalesInvoice/Items/Item’)
WITH
(ProductCode INT,
Quantity INT,
UnitPrice MONEY,
ProductName VARCHAR(50))
No flag parameter included in the code above indicates that we only want to see attribute data. Values are present for all fields except the element (<productname>), which shows all null values in our tabular result</productname>
This is because when you use OPENXML and you’re matching on an exact field name, SQL Server looks for attributes by default. ProductName is the precise field name to search for this element. However, our code isn’t searching for an element - it’s searching for attribute data only (Figure 4.12).
DECLARE @iNum INT
SET @iNum = 1 SELECT * FROM
OPENXML (@iNum, ‘SalesInvoice/Items/Item’, 1)
WITH
(ProductCode INT,
Quantity INT,
UnitPrice MONEY,
ProductName VARCHAR(50))
Now we see element data because our flag is set to 2 (Figure 4.13). Notice it found a <productname> as a child element of the rowpattern. There are no values for ProductCode, Quantity, or UnitPrice, as those are all attributes (Figure 4.10).</productname>
DECLARE @iNum INT
SET @iNum = 1 SELECT * FROM
OPENXML (@iNum, ‘SalesInvoice/Items/Item’, 2)
WITH (ProductCode INT, Quantity INT, UnitPrice MONEY,
ProductName VARCHAR(50))
With our flag set to 3, we finally get all of the data points we want to see. The parameter value of 3 instructs our code to look for both attribute and element data (Figure 4.14).
DECLARE @iNum INT
SET @iNum = 1 SELECT * FROM
OPENXML (@iNum, ‘SalesInvoice/Items/Item’, 3)
WITH
(ProductCode INT,
Quantity INT,
UnitPrice MONEY,
ProductName VARCHAR(50))
This optional flag is very handy for bringing in all of the attribute and element data we want. Notice that we are able to accomplish this without needing to specify any custom column patterns in any of our examples (Figure 4.11- Figure 4.14). Again, this is due to the fact that our code specifies field names which match the field names as they appear in the XML document. For any fields which are aliased (i.e., whose names do not match the field names in the XML document), we would need to specify a column pattern in order for our attribute and/or element values to appear in our tabular result (Figure 4.15).
