SQL Server 2005 XML - Using OpenXML to load complex XML data?


My data contains both attribute and element-centric data, as well as multiple nested child nodes containing multiple child (grandchild) nodes.

But as usual the case, after hours of Googling, no one seems to want to address this topic on any level deeper than novice/beginner, so I again find myself addressing my question to the experts. :-)

Ok, using xml from a previous question, consider the following example:

I have an xml document that I am reading into sql server, in order to insert/update rows in an existing table.

The XML document might look like:

               <Name Type="Professional">John Doe</Name>
               <Address Info>
                    <Address Type="Residential">
                          <AddressLine>1234 Main St.</AddressLine>
                          <AddressLine>Lean-to #146B.</AddressLine>
                          <AddressLine>Anchorage, AK. 90049</AddressLine>
                    <Address Type="Business">
                         <AddressLine>5678 Frozen Tundra</AddressLine>
                         <AddressLine>Russian Vista Office Park, Igloo 2012</AddressLine>
                         <AddressLine>Anchorage, AK. 90051</AddressLine>
               </Address Info>

So, this document invloves two-levels of complexity:
1. Mixed-Mode data. i.e. Attribute-centric and Element-centric
2. Multiple nested child nodes containing multiple child nodes.

How would the "WITH" clause of the Openxml statement look for the
above document?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I think you are going to need multiple select/openxml statments to query different levels of information.

The first would be to query the Name and Phone Info.
The second would query the Address info.
-- Name and Phone
FROM   OPENXML (@idoc, '/XMLDoc/MyContacts/Contact/Phone',2)
         WITH (Name     varchar(100) '../Name',
			   NameType varchar(100) '../Name/@Type',
			   PhoneType varchar(20) 'Type',
			   PhoneNumber varchar(20) 'Number')

FROM   OPENXML (@idoc, '/XMLDoc/MyContacts/Contact/AddressInfo/Address/AddressLine',2)
         WITH (Name     varchar(100) '../../../Name',
			   NameType varchar(100) '../../../Name/@Type',
			   AddressType varchar(20) '../@Type',
			   AddressLine varchar(100) '.')

Open in new window

ibcmisAuthor Commented:

By virtue of your merely attempting to answer the question, almost makes you deserving of the points. ;-)

As I researched further, it began to dawn on me that I might have to take multiple "bites of the apple" to get this thing read in, but was hoping it wouldn't come to that.

I will leave this question up another day or two, to see if someone is of a different opinion.

After that, given the code you offered,  I'd be happy to accept your answer as the solution, if the code is essentially correct.

Here is an option that requires one select statement.  However, it is not as flexible as it predefines the values of Address Type and Phone Type and limits the number of address lines to three  (although you could increase this to four if needed).
FROM   OPENXML (@idoc, '/XMLDoc/MyContacts/Contact',2)
         WITH (Name     varchar(100) 'Name',
			   NameType varchar(100) 'Name/@Type',
			   CellPhone varchar(20) 'Phone[./Type = "Cell"]/Number',
			   HomePhone varchar(20) 'Phone[./Type = "Home"]/Number',
			   ResidentialAddress_Line1 varchar(20) 'AddressInfo/Address[@Type = "Residential"]/AddressLine[1]',
			   ResidentialAddress_Line2 varchar(20) 'AddressInfo/Address[@Type = "Residential"]/AddressLine[2]',
			   ResidentialAddress_Line3 varchar(20) 'AddressInfo/Address[@Type = "Residential"]/AddressLine[3]',
			   BusinessAddress_Line1 varchar(20) 'AddressInfo/Address[@Type = "Business"]/AddressLine[1]',
			   BusinessAddress_Line2 varchar(20) 'AddressInfo/Address[@Type = "Business"]/AddressLine[2]',
			   BusinessAddress_Line3 varchar(20) 'AddressInfo/Address[@Type = "Business"]/AddressLine[3]')

Open in new window

10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Anthony PerkinsCommented:
>>I have an xml document that I am reading into sql server, in order to insert/update rows in an existing table.<<
To be honest, it really does not matter how you extract the data (see below for another way to do it), as  they are probably going to end up in temporary tables in any case.  What matters is what do your Production tables look like and what data do they expect/need.

Incidentally, your Xml is not well-formed (node names cannot have spaces in them), but perhaps it is not the real one.
FROM   OPENXML (@idoc, '/XMLDoc/MyContacts/Contact/AddressInfo/Address/AddressLine', 2) WITH (
			Name     varchar(100) '../../../Name',
			NameType varchar(100) '../../../Name/@Type',
			CellPhone varchar(20) '../../../Phone[./Type = "Cell"]/Number',
			HomePhone varchar(20) '../../../Phone[./Type = "Home"]/Number',
			AddressType varchar(20) '../@Type',
			Address_Line varchar(20) '.')

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ibcmisAuthor Commented:

Thanks for your solution. It solves the problem perfectly.

You are correct, the xml is not my project's real xml.

>>To be honest, it really does not matter how you extract the data (see below for another way to do >>it), as  they are probably going to end up in temporary tables in any case.  What matters is what do >>your Production tables look like and what data do they expect/need.

From a purely technical standpoint, I cannot disagre with your statement. However, regarding the means of extracting the data, I'll have to disagree, because some solutions may allow for more efficiency or flexibility, or if nothing else, less typing. ;-)

I wanted to use an example that would illustrate multi-level xml shredding syntax. In real life, a table fed by such an xml structure would have been denormalized.

Lundnak, thanks again for your solution. It also worked.
What happens to the "Accepted Solution" query if the XML has not AddressLine children?  The query will return zero records.

I honestly think your best solution is to break up the query.
Sorry to be sore about this, but ACPerkins answer is very similar to mine (notice the variable name).  That is fine with me.  I have nothing against him.  You, however, chose not to award any points to me for the answers that I provided.  Very uncool.
ibcmisAuthor Commented:

Please accept my sincerest apologies.

Although my company account has been around for awhile, I am very new to this site.

I actually went back and forth over an hour, trying to decide whether to split the points. I actually thought that BOTH of you deserved points, and it's quite ironic that after spending so much time trying NOT to, I ended up violating etiquette anyway. I knew I was going to use acperkin's solution in this instance, even though I also saw application for yours in another scenario. So I definitely understand your annoyance.

I thought if I split the points, I'd be criticized for not giving full credit to the solution I planned to use, so  even though I didn't feel good about it, I assigned them all to acperkins, even though the fairness in me, wanted to reward your effort.

Again, my apologies.

Please forgive my newbie ignorance...Next time I will know better.
Please heed the warning that I posted about ACperkin's solution.  If a contact node does not have any addressline child nodes then the Contact node will not be displayed in the recordset.

Is it possible that you will have some Contacts with only phone numbers?  If so, then these nodes will be ignored.

I also considered posting this as a solution, but I found it to be substandard because of the possibility of ignoring records.

Also, it is always best to split points if more than one person has contributed worthwhile work towards the answer.  You will never upset experts if you split points.
ibcmisAuthor Commented:
Thanks for reminding me of your earlier point...I had gotten completely caught up in addressing my breach of etiquette.

In this particular instance, there is no danger of the complete openxml path not existing in the data, but I definitely see your point, and will make a mental note for future reference.

Thanks for your gracious followup, and the tip on points splitting.

Both are greatly appreciated.
Anthony PerkinsCommented:
Sorry I have been away, so I missed the excitement.  To be honest I was not expecting any points (and should have said so), I was merely trying to show a different approach so that you or more importantly anyone else reading this later would get a more complete solution.

Feel free to post a message in Community Support to have this question re-opened so that you can split points.
ibcmisAuthor Commented:
My apologies for it having taken so long to get back to this...Killer project, crazy timeline.


ACperkins, thanks for your reply. It hadn't occurred to me to reopen the question.

I also appreciate your sense of fairness.

Although you "didn't expect" any points, your solution's syntax was the one that I used to successfully solve my problem. But taking into account the similarity to lundnak's original solution, and the additional knowledge and insight he provided (which I'm sure I will make use of in the future), I've decided to split the points evenly.

Thanks to you both...This has been a learning experience, in more ways than one.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.