Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I saw a question recently about querying an XML document. Certainly avoid this in the database if you can, but there are times you need to. Rather than link to the post, I wanted to show the basics of how you query a node.

Let’s suppose I have an XML document like this:

<Order>
  <OrderID>4FB9</OrderID>
  <ORderDate>2019-07-20-00.31.23.000000</ORderDate>
  <Status>Open</Status>
  <Customer>
    <CustomerName Type=”Individual”>
      <FirstName>Jon</FirstName>
      <LastName>Doe</LastName>
    </CustomerName>
  </Customer>
  <Customer Type = “Company”>
    <CustomerName>
      <CompanyName>Acme</CompanyName>
      <Account>12345</Account>
    </CustomerName>
  </Customer>
  </Order>

Now, I saw someone query this with code like this to get the OrderID.

DECLARE @xml XML;
SET @xml = N’
<Order>
  <OrderID>4FB9</OrderID>
  <ORderDate>2019-07-20-00.31.23.000000</ORderDate>
  <Status>Open</Status>
  <Customer>
    <CustomerName Type=”Individual”>
      <FirstName>Jon</FirstName>
      <LastName>Doe</LastName>
    </CustomerName>
  </Customer>
  <Customer Type = “Company”>
    <CustomerName>
      <CompanyName>Acme</CompanyName>
      <Account>12345</Account>
    </CustomerName>
  </Customer>
  </Order>
‘;

SELECT
      t.b.value(‘(ORDERID)[1]’, ‘NVARCHAR(100)’) AS MSGID
  FROM
    @xml.nodes(‘/Order’) t(b);

This doesn’t work.

The reason this doesn’t work is that XML is case sensitive. Meaning ORDERID != OrderID. The former is in the query, the latter in the XML document. If I change the query, this works (note I have OrderID below).

This would also apply to the .Nodes call. If I had .ORDER, this also wouldn’t work.

The @xml.nodes() call determines the root at which I’ve essentially set the document. I could have this as /Order/Customer if I wanted. In that case, I couldn’t access the OrderID. The OrderID isn’t below the Customer node.

However, from below Customer, I can get to the names.

There is a lot more to know about XML, but you can experiment with the various nesting levels by including different paths. I’ll show a few more things in another post.

SQLNewBlogger

Querying XML is hard, and can be frustrating as the document size grows and complexity grows. However, this is a good way to showcase your skills (or build them), but tackling different query questions or challenges and writing about them.

Hint: this will also help solidify your XML skills.