A deeper dive into XML in SQL Server

When I was younger, I remember my family taking a camping trip near a lake with trout. As best I can remember, the lake was stocked with rainbow and brown trout. When we visited the lake to fish there was a crowd of people along one of the shores. There were a mass of small trout in the water, and everyone was trying to get one to bite. Very few of the people were succeeding. Seeing the fish there, I joined in the group, desparate to try to get one of the 8″ – 10″ fish to bite. Suddenly from a shoreline halfway across the lake, I heard a triumphant cry. Looking up, I saw a man holding an enormous trout in the air. He’d caught the fish by avoiding the crowd of people and small fish, and exploring a new shoreline. I learned two lessons that day. First, if you target smaller fish because you know where they are, you’ll never catch the big ones. Second, if you never explore unfamiliar water you’ll miss out on some good fishing.
One of our developers was recently pulling data from SQL Server to create a catalog feed for a website. This feed included our products, which have a parent child hierarchy between a base product and the skus beneath that product. His plan was to create two stored procedures – one for the base product and one for the skus. Then in his code he was going to take the results of those two stored procedures and merge them into a single XML document.
I had recently taken a SQL quiz and ran into the hierarchy data type, and thought this might be a good way to experiment with using it. After doing some initial work, I found that the hierchary id was overkill for this situation. The Hierarchy data type seems to work best when you have several levels you want be able to delve in to. Instead, I decided to attempt the feed directly in SQL using the FOR XML clause. I have used this clause a few times before, but never to build something as complex as this document. Here are some things I did or observed that may be helpful:
1. Concatenating XML
The final output document had several sections in it that I was forced to create with separate SELECT statements. I couldn’t get these together into the same XML document by unioning or concatenating them together. Instead I had to place them each into variables, then SELECT the variables together in a separate statement that used the FOR XML clause.

DECLARE @xmlheader XML, @xmlnonskuitem AS XML, @xmlsizeonlyitem AS XML, @xmlcoloronlyitem AS XML, @xmlcolorandsizeitem AS XML, @xmlskus AS XML;

SELECT @xmlheader =  ...;
SELECT @xmlnonskuitem = ...;
SELECT @xmlcoloronlyitem = ...;
SELECT @xmlsizeonlyitem = ...;
SELECT @xmlcolorandsizeitem = ...;
SELECT @xmlskus = ...;

SELECT @xmlheader, @xmlnonskuitem, @xmlcoloronlyitem, @xmlsizeonlyitem, @xmlcolorandsizeitem, @xmlskus
--End root
FOR XML RAW () ;

2. Building depth into the XML tree
The xml we were creating had several child elements, some of which needed to be repeated for each variation, and some of which were used to encapsulate all variants for a particular base product. To get this layering, I had to create some of the elements with varchar characters that could be converted to XML. Complicating this, I needed to create XML inside of those text characters, and join them together. The general pattern for this is to convert the inner most XML to varchar, then concatenate those together with the hard coded elements, then convert the whole thing back to XML.

SELECT @xmlsizeonlyitem
    =
(
    SELECT RTRIM(LTRIM(i.[Field2])) AS "@product-id"
         , CONVERT(
                      XML
                    , '<attributes><variation-attribute attribute-id="size" variation-attribute-id="size"><display-name>Size</display-name><variation-attribute-values>'
                      + CONVERT(VARCHAR(MAX)
                              , (
                                    SELECT LTRIM(RTRIM(s.size)) AS [@value]
                                         , RTRIM(LTRIM(s.size)) AS [display-value]
                                    FROM db.SCHEMA.tblSku AS s
                                    WHERE s.Field1 = 125
                                          AND s.Field2 = i.Field2
                                    FOR XML PATH('variation-attribute-value'), TYPE
                                )
                               ) + '</variation-attribute-values></variation-attribute></attributes><variants>'
                      + CONVERT(VARCHAR(MAX)
                              , (
                                    SELECT RTRIM(LTRIM(s.Field2)) + ' ' + RTRIM(LTRIM(s.Field3)) AS [@product-id]
                                    FROM db.SCHEMA.tblSku AS s
                                    WHERE s.Field1 = 125
                                          AND s.Field2 = i.Field2
                                    FOR XML PATH('variant'), TYPE
                                )
                               ) + '</variants>'
                  )
    FROM db.SCHEMA.tblBaseItem i
    WHERE i.Field1 = 125
          AND i.Field2 = 'BaseItemId'
    GROUP BY i.Field1
           , i.Description
    FOR XML PATH('product')
);

3. Elements versus attributes
It’s very easy to change the field you are returning from the database to either an element or an attribute. For attributes you put the @ sign in the front of the name. For elements, you just leave the name as is.

SELECT @xmlcolorandsizeitem = (SELECT RTRIM(LTRIM(i.[Field2])) AS "@product-id"
    , RTRIM(LTRIM(i.Description)) AS [display-name]
FROM db.SCHEMA.baseitemtable i
    WHERE i.field1 = 125
          AND i.field2 = '7972'
         GROUP BY i.field1, i.Description
FOR XML PATH('product')) ;

20180216 XML Elements vs Attributes
4. Renaming ROOT and ROW
You can rename the ROOT element to something else by putting that in the parenthesis after ROOT.
You can also rename the first element, usually defaulted as ROW, by putting the name in the parenthesis after RAW or PATH. This does not work in EXPLICIT or AUTO modes, which don’t have a ROW element.

FOR XML RAW ('catalog') ROOT ('catalogs');

20180216 XML Root and Raw.PNG
5. Adding namespace
To add a namespace to the XML, you can use the WITH XMLNAMESPACES clause. This only works for RAW, AUTO, or PATH modes.

WITH XMLNAMESPACES('http://www.company.com/xml/impex/catalog/2106-1-31' AS Co)

20180216 XML Namespace

I hope these tips give you a better understanding of how to work with XML in SQL.

Advertisements

Managing Exchange in Office 365 with Powershell

I have a lot of memories of fishing growing up.  Some are great and some are not.  One memory I have was seeing a giant muskie in the water while fishing as a child.  I was very inexperienced, and didn’t know anything about muskie fishing.  Well, the fish in the water was just sitting down near the bottom.  I cast a shad rap out a ways, then retrieved it past the muskie’s face.  The fish acted as if it didn’t even see the lure.  I figured I’d try a different lure, so I pushed the swivel lock to open it up and it broke.  The whole wire simply snapped in half.  I was crestfallen.  Here I had a giant fish right below me, and I had no way to put a lure onto my line.  You see, this was before I had learned to tie a fishing knot.  Disappointed, I came back to the dock with a story about how I missed out on a fish.

I used that experience as a lesson though, and soon learned to tie a fishing know so well that I could do it in seconds without even looking.

Working in IT can often mean a range of duties and responsibilities, particularly with smaller companies.  Today I was asked to fix an issue in Office 365.  The user had a shared mailbox she could send out of, but the emails did not show up in her Sent folder.  After some online searching I found this Microsoft blog post that described how to enable this feature for an O365 shared mailbox.  I’ve worked with Powershell, but I’ve never administered O365 with it, so it was a good learning experience.

First, I had to connect to Exchange Online.  I followed the instructions on this Technet Page.  First I used the Get-Credential command to input my O365 credentials.

20180212 Get Credential.PNG

Next I had to create a session with Exchange Online.

20180212 Connect To Exchange

Next I had to import that session into my local session.

20180212 Import PS Session

From here I attempted to run the Set-Mailbox command, but I came up with an error:

20180212 SetMailbox Failure

This was because I was using the wrong account.  I have a local user account and a local admin account.  Well, my local user account is a Global Admin in O635 (go figure), but my local admin account is nothing.  So I closed it everything down, making sure to use the Remove-PSSession $Session command as suggested by the page I linked to earlier.  Then I went through all the steps again, this time inputting my user account instead of my admin account.  This time, the command was successful:

20180212 SetMailbox Success

To see whether you have permission to make this change, you can check if your user is a Global Admin in the O365 portal:

20180212 O365 Global Admin.PNG

Using this method I was able to solve the user’s problem.