Shredding XML Conditionally in SQL Server

Question

Consider the following XML file:

<?xml version="1.0" encoding="ISO-8859-1"?>
<Input Control="1234567890">
   <Patient>
      <AccountID>[email protected]</AccountID>
      <Name>
         <Title>Mr</Title>
         <First>John</First>
         <Middle>J</Middle>
         <Last>Smith</Last>
      </Name>
      <Addresses>
         <Address Type="Billing">
            <Line1>123 Main St</Line1>
            <Line2>Unit #1</Line2>
            <City>Anytown</City>
            <State>MD</State>
            <PostalCode>78470</PostalCode>
            <Country>USA</Country>
            <Zone>TR89</Zone>
         <Address Type="Contact">
            <Line1>55 Main St</Line1>
            <City>Anytown</City>
            <State>MD</State>
            <PostalCode>78470</PostalCode>
            <Country>USA</Country>
            <Zone>TR89</Zone>
         </Address>
      </Addresses>
      <Phones>
         <Phone Type="Daytime">555-221-2525</Phone>
         <Phone Type="Evening">555-355-1010</Phone>
      </Phones>
      <Selects>
         <Select Name="Current">0</Select>
      </Selects>
   </Patient>
</Input>  

I need to 'shred' the XML (using XPath/XQuery) into the following table:

  Account    Nam_Pr   Nam_Fst  Nam_Lst  Adrs1Main  Adrs2Main  CityMain ... 
     1        Mr.      John     Smith  123 Main St  Unit #1    Anytown

The issue is, that I need to populate the AdrsMain fields with the data from the Address Type="Billing element only if it's present in the file. Otherwise, the AdrsMain fields are populated from the Address Type="Contact" element. How can I accomplish this?

Answer 1

Turns out it was a little bit harder than I imagined, so I'll go ahead and answer this message. I don't recall xquery from attributes requiring me to do any conversions to null because (I thought) the values returned were already null. Because of this, you might need to convert some empty strings back to null. Luckily, I found about a cool little operator called nullif.

But here's the idea.

create table docs ( id int primary key, xmlbit xml);

assume your document files are added and then you can do

with things (BillingLine1,ContactLine1)  as
(
SELECT 
nullif(cast(xmlbit.query('data(/Input/Patient/Addresses/Address[@Type="Billing"]/Line1)') as varchar(100)),'') as BillingLine1,
nullif(cast(xmlbit.query('data(/Input/Patient/Addresses/Address[@Type="Contact"]/Line1)') as varchar(100)),'') as ContactLine1
from docs  
 )
select BillingLine1, ContactLine1, Coalesce(BillingLine1,  ContactLine1) as needed from things;

Demo: http://sqlfiddle.com/#!3/b3fbd/15

There are probably other ways you could do this. I have a feeling if you really wanted to, you could do everything within the xquery/xpath statements, and it may even be more advantageous, but I'm not sure.

Edit: Please do note the size of the varchars I've cast them to; you may need to adjust to see fit.