Shredding XML to Tables While Providing Parent-Child Keys

In my last post, I demonstrated how to shape and bend XML from your existing tables in SQL Server. In this post I am going to go the other direction and shred the xml directly into SQL tables with a desired structure. I will also create a parent-child relationship between entities where no keys previously existed in the xml.

This approach uses the older OPENXML mechanisms and is distinct from the value() and node() methods introduced later. I believe this approach to be superior and easier to construct than that demonstrated by Adam Machanic.

• I'm assuming here that you've loaded the xml into the @xmlDocument variable (as in my previous example)
• You need a document handle (@XmlDocumentHandle). As long as this handle remains open, the id's returned for each node do not change.
• Use the OPENXML function with the @XmlDocumentHandle and create an XPath query that finds the data for the you want.
• The value of 9 means to map attributes to your defined table, and handles really big documents.
• The table definition is in the WITH clause
• The @mp:id and @mp:parent are the identifier for the individual node and its parent. Every node in the document has a unique value that doesn't change; these are used to make our parent-child relationships work.

Here is some sample XML and SQL:

	select @xmlDocument
	DECLARE @XmlDocumentHandle int
	EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @XmlDocument
	SELECT * FROM OPENXML (@XmlDocumentHandle, '/Students/Student', 9)
	WITH ( StudentId int '@mp:id',
	Id uniqueidentifier,
	FirstName varchar(50),
	LastSurname varchar(50) )
	SELECT * FROM OPENXML (@XmlDocumentHandle, '/Students/Student/Address', 9)
	WITH ( AddressId int '@mp:id',
	StudentId int '@mp:parentid',
	StreetNumberName varchar(50))
	SELECT * FROM OPENXML (@XmlDocumentHandle, '/Students/Student/Language', 9)
	WITH ( LanguageId int '@mp:id',
	StudentId int '@mp:parentid',
	LanguageDescriptorId int)
	SELECT * FROM OPENXML (@XmlDocumentHandle, '/Students/Student/Language/LanguageUse', 9)
	WITH ( LanguageUseId int '@mp:id',
	LanguageId int '@mp:parentid',
	LanguageUseTypeId int)
	EXEC sp_xml_removedocument @XmlDocumentHandle

view raw ShredXmlToTables.sql hosted with ❤ by GitHub

	<Students>
	<Student PersonalTitlePrefix="Mrs" FirstName="Shelby" MiddleName="Ruby" LastSurname="Barajas" SexTypeId="1" BirthDate="2005-12-15" HispanicLatinoEthnicity="1" OldEthnicityTypeId="1" LoginId="sbarajas" StudentUniqueId="605704" Id="1322C606-D644-497B-8548-207933921855">
	<Address AddressTypeId="10" StreetNumberName="654 Mission Hills" City="Grand Bend" StateAbbreviationTypeId="54" PostalCode="78834" NameOfCounty="WILLISTON" />
	</Student>
	<Student PersonalTitlePrefix="Mrs" FirstName="Sarah" LastSurname="Stevens" SexTypeId="1" BirthDate="2005-06-12" HispanicLatinoEthnicity="0" OldEthnicityTypeId="1" LoginId="sstevens" StudentUniqueId="555555" Id="9F5B105B-0B03-4508-B7B4-AD1731A85B6E">
	<Address AddressTypeId="3" StreetNumberName="123 Simons Way" City="Grand Bend" StateAbbreviationTypeId="54" PostalCode="78834" NameOfCounty="WILLISTON" />
	</Student>
	<Student PersonalTitlePrefix="Mr" FirstName="Gilbert" MiddleName="Jesse" LastSurname="Fritz" SexTypeId="2" BirthDate="2005-12-16" HispanicLatinoEthnicity="1" OldEthnicityTypeId="4" LoginId="gfritz" StudentUniqueId="604888" Id="5757B9B9-C4AD-4B6D-B1A0-056B2D08B7BD">
	<Address AddressTypeId="3" StreetNumberName="16 Green Hague Way" City="Grand Bend" StateAbbreviationTypeId="54" PostalCode="78834" NameOfCounty="WILLISTON" />
	</Student>
	<Student PersonalTitlePrefix="Mrs" FirstName="Debbie" LastSurname="Savage" SexTypeId="1" BirthDate="2005-06-12" HispanicLatinoEthnicity="0" OldEthnicityTypeId="1" LoginId="dsavage" StudentUniqueId="605372" Id="DE2FF0BA-F23B-4323-87C9-FA4AF3C3FA44">
	<Address AddressTypeId="3" StreetNumberName="123 Simons Way" City="Grand Bend" StateAbbreviationTypeId="54" PostalCode="78834" NameOfCounty="WILLISTON" />
	</Student>
	<Student PersonalTitlePrefix="Mr" FirstName="Sandro" MiddleName="Francisco" LastSurname="Miguel" SexTypeId="2" BirthDate="2005-12-16" HispanicLatinoEthnicity="1" OldEthnicityTypeId="4" LoginId="smiguel" StudentUniqueId="777777" Id="AFE73075-5460-4FBD-858B-E8864E8E1A38">
	<Address AddressTypeId="3" StreetNumberName="16 Green Hague Way" City="Grand Bend" StateAbbreviationTypeId="54" PostalCode="78834" NameOfCounty="WILLISTON" />
	</Student>
	<Student PersonalTitlePrefix="Ms" FirstName="Alicia" MiddleName="Angela" LastSurname="Dennis" SexTypeId="1" BirthDate="2005-11-09" HispanicLatinoEthnicity="1" OldEthnicityTypeId="4" LoginId="adennis" StudentUniqueId="604903" Id="47E92DBE-AE26-4F52-B532-5F8BD9E02826">
	<Address AddressTypeId="3" StreetNumberName="25 North Clarendon Boulevard" City="Grand Bend" StateAbbreviationTypeId="54" PostalCode="78834" NameOfCounty="WILLISTON" />
	<Language LanguageDescriptorId="524">
	<LanguageUse LanguageUseTypeId="1" />
	</Language>
	</Student>
	</Students>

view raw Source.xml hosted with ❤ by GitHub

And here are some results: (sorry for the ugly tables)

StudentId	Id	FirstName	LastSurname
2	1322C606-D644-497B-8548-207933921855	Shelby	Barajas
21	9F5B105B-0B03-4508-B7B4-AD1731A85B6E	Sarah	Stevens
39	5757B9B9-C4AD-4B6D-B1A0-056B2D08B7BD	Gilbert	Fritz
58	DE2FF0BA-F23B-4323-87C9-FA4AF3C3FA44	Debbie	Savage
76	AFE73075-5460-4FBD-858B-E8864E8E1A38	Sandro	Miguel
95	47E92DBE-AE26-4F52-B532-5F8BD9E02826	Alicia	Dennis

AddressId	StudentId	StreetNumberName
14	2	654 Mission Hills
32	21	123 Simons Way
51	39	16 Green Hague Way
69	58	123 Simons Way
88	76	16 Green Hague Way
107	95	25 North Clarendon Boulevard

LanguageId	StudentId	LanguageDescriptorId
114	95	524

LanguageUseId	LanguageId	LanguageUseTypeId
116	114	1