Search code examples
arrayssql-serverxmlnodeqxmlquery

SQL Server parse array of XML Nodes using XMLQUERY

I am trying to parse an XML Array of nodes, which is a nvarchar(max) column converted to XML, and I've searched using google and searched stackoverflow, but I'm not finding any examples that may help me out with this.

What I have is an XML value stored as nvarchar(max) with the following format:

<?xml version="1.0" encoding="utf-16"?>
<ArrayOfCmnStatusRuleOverride xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <cmnStatusRuleOverride xsi:type="cmnCoveredByOverride">
        <Start>2020-05-28T17:00:00</Start>
        <End>2020-05-29T07:00:00</End>
        <SuspendExpiration>0001-01-01T00:00:00</SuspendExpiration>
        <subID>103</subID>
        <listID>4016</listID>
        <Description>Jane Doe</Description>
    </cmnStatusRuleOverride>
    <cmnStatusRuleOverride xsi:type="cmnCoveredByOverride">
        <Start>2020-05-26T17:00:00</Start>
        <End>2020-05-28T07:00:00</End>
        <SuspendExpiration>0001-01-01T00:00:00</SuspendExpiration>
        <subID>103</subID>
        <listID>4014</listID>
        <Description>Joe Blow</Description>
    </cmnStatusRuleOverride>
    <cmnStatusRuleOverride xsi:type="cmnCoveredByOverride">
        <Start>2020-05-25T17:00:00</Start>
        <End>2020-05-26T07:00:00</End>
        <SuspendExpiration>0001-01-01T00:00:00</SuspendExpiration>
        <subID>103</subID>
        <listID>4016</listID>
        <Description>Jane Doe</Description>
    </cmnStatusRuleOverride>
</ArrayOfCmnStatusRuleOverride>

I'm using the following SQL to parse the content, which is close to what I need, but the cross applies are causing me problems

declare @Status xml, @Override nvarchar(100),@ORStart nvarchar(50), @OREnd nvarchar(50)
set @Status = 
(select b.Overrides from Database.dbo.Descriptions as a
inner join Database.dbo.Listings as b on a.listID = b.listId
inner join Database.dbo.Clients as c on b.subId = c.subId and cast(c.ClientNumber as nvarchar) = '2195956693'
--cross apply @Status.nodes('/ArrayOfCmnStatusRuleOverride/cmnStatusRuleOverride/Description') as T(Loc)
where a.Description = 'Jack Sprat')

select T.OVR.query('.') 'Override'--, T1.STT.query('.') 'Start'--, T2.ENND.query('.') 'End'
from Database.dbo.Descriptions as a
inner join Database.dbo.Listings as b on a.listID = b.listId
inner join Database.dbo.Clients as c on b.subId = c.subId and cast(c.ClientNumber as nvarchar) = '9876543210'
cross apply @Status.nodes('/ArrayOfCmnStatusRuleOverride/cmnStatusRuleOverride/Description') as T(OVR)
--cross apply T.OVR.nodes('/ArrayOfCmnStatusRuleOverride/cmnStatusRuleOverride/Start') as T1(STT)
--cross apply T.OVR.nodes('/ArrayOfCmnStatusRuleOverride/cmnStatusRuleOverride/End') as T2(ENND)
where a.Description = 'Jack Sprat'

With the second and third xml queries commented out, I get 3 results, as I would expect.

  1. Jane Doe
  2. Joe Blow
  3. Jane Doe

If I uncomment the second query, then I get 9 results as follows

    Override        Start
  1. Jane Doe 2020-05-28T17:00:00
  2. Jane Doe 2020-05-26T17:00:00
  3. Jane Doe 2020-05-25T17:00:00
  4. Joe Blow 2020-05-28T17:00:00
  5. Joe Blow 2020-05-26T17:00:00
  6. Joe Blow 2020-05-25T17:00:00
  7. Jane Doe 2020-05-28T17:00:00
  8. Jane Doe 2020-05-26T17:00:00
  9. Jane Doe 2020-05-25T17:00:00

And, of course, if I uncomment the third query, I get 27 rows. Any tips on what I'm doing wrong? I've worked with this since yesterday morning trying to figure this out, and so close, yet so far away.

Solution

  • Figured it out.

    select T.OVR.query('./Description') 'Override', 
T.OVR.query('./Start') 'Start', 
T.OVR.query('./End') 'End'
from Database.dbo.Descriptions as a
inner join Database.dbo.Listings as b on a.listID = b.listId
inner join Database.dbo.Clients as c on b.subId = c.subId and cast(c.ClientNumber as nvarchar) = '9876543210'
cross apply @Status.nodes('/ArrayOfCmnStatusRuleOverride/cmnStatusRuleOverride') as T(OVR)
where a.Description = 'Jack Sprat'