SQL Server 2005 OpenXML - How do you load XML data containing nested elements/tags?

Hi,

Amazingly, I have been unable to easily find an answer to this simple question.

The problem is that there are plenty of blogs/articles on using OpenXml, but the examples use the lamest, most simplistic, single-level XML documents to load.

Ok, for example, I have table Contacts with the following colums:

ContactName
PhoneType
PhoneNum

The XML parameter data looks like:

<XMLDoc>
     <Contacts>
          <Name>John Doe</Name>
          <Phone>
               <Type>Cell</Type>
               <Number>555-555-5555</Number>
          </Phone>
     </Contacts>
</XML Doc>

So, in the SPROC, to load the input XML, the SQL would resemble:
...
INSERT Contacts(
     ContactName,
     PhoneType,
     PhoneNum
)
SELECT Name,
     Type
     Number
FROM OPENXML (@XML, '/Contacts',2)
WITH ( Name nvarchar (100),
           ?
           ?
)

My question is, how do you specify the data in nested "Phone" element?

Any and all help greatly appreciated.
Tony
ibcmisAsked:
Who is Participating?
 
nativCommented:
SELECT Name, PhoneType
 FROM OPENXML (@idoc, '/XMLDoc/Contacts',2)
WITH ( Name nvarchar (100),
 PhoneType varchar(10) 'Phone/Type',
 PhoneNum varchar(10) 'Phone/Num',
)
0
 
ibcmisAuthor Commented:
Hi nativ,

Thank you for your response.

Your code looks like it should work, and as soon as I try it, I will "accept" it as the solution.

Thanks again.

0
 
Anthony PerkinsCommented:
Just some minor typos in your Xml and the solution provided (please no points):
<XMLDoc>
     <Contacts>
          <Name>John Doe</Name>
          <Phone>
               <Type>Cell</Type>
               <Number>555-555-5555</Number>
          </Phone>
     </Contacts>
</XMLDoc>

SELECT  Name,
        PhoneType,
        PhoneNum
FROM    OPENXML (@idoc, '/XMLDoc/Contacts',2)
WITH ( Name nvarchar (100),
 PhoneType varchar(10) 'Phone/Type',
 PhoneNum varchar(10) 'Phone/Number')

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Anthony PerkinsCommented:
Having said that, you may find the given an Xml structure like this:

<XMLDoc>
     <Contacts>
          <Name>John Doe</Name>
          <Phone>
               <Type>Cell</Type>
               <Number>555-555-5555</Number>
          </Phone>
          <Phone>
               <Type>Business</Type>
               <Number>666-666-6666</Number>
          </Phone>
     </Contacts>
</XMLDoc>

The following OPENXML query is more generic and will give you all the phones, not just the first one:
SELECT  Name,
        PhoneType,
        PhoneNum
FROM    OPENXML (@idoc, '/XMLDoc/Contacts/Phone', 2) WITH (
		Name nvarchar (100) '../Name',
		PhoneType varchar(10) 'Type',
		PhoneNum varchar(10) 'Number')

Open in new window

0
 
ibcmisAuthor Commented:
Nativ,

Thank you very much. The snippet you provided was what I needed.

Tony
0
 
ibcmisAuthor Commented:
Short, sucinct, and correct. Couldn't ask for more.
0
 
ibcmisAuthor Commented:
acperkins,

Thank you for your additional example...I believe you read my mind.

Although my current project doesn't require it, I was also thinking of just the scenario you astutely addressed in your example. I just didn't want to muddy the waters by bringing it up.

Thanks again for your contribution.

Tony
0
 
Anthony PerkinsCommented:
Thank you and I appreciate the comments and the explanation.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.