SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Basic XML Node Query–#SQLNewBlogger

 

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.

2016-06-15 13_09_07-Photos

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).

2016-06-15 13_11_23-Photos

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

2016-06-15 13_11_54-Photos

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.

2016-06-15 13_13_21-Photos

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

2016-06-15 13_14_05-Photos

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.


Filed under: Blog Tagged: SQLNewBlogger, syndicated, T-SQL, xml

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Comments

Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...