How to extract multiple xml element into individual tables?

Question

I have XML data like this

DECLARE @input XML = 
  '<LicensingReportProcessResult>
      <LicensingReport>
        <Address key="3845HoopaLnLasVegasNV89169-3350U.S.A.">
            <LineOne>3845 Hoopa Ln</LineOne>
            <CityName>Las Vegas</CityName>
            <StateOrProvinceCode>NV</StateOrProvinceCode>
            <PostalCode>89169-3350</PostalCode>
            <CountryCode>U.S.A.</CountryCode>
        </Address>
        <Person key="PersonPRI711284842">
            <ExternalIdentifier>
                <TypeCode>NAICProducerCode</TypeCode>
                <Id>8001585</Id>
            </ExternalIdentifier>
            <BirthDate>1961-07-29</BirthDate>
        </Person>
   </LicensingReport>
</LicensingReportProcessResult>'

My T-SQL code to extract one specific set of elements:

-- extract into temp table
INSERT INTO #Address
    SELECT 
        Tbl.Col.value('@Address', 'NVARCHAR(100)'),
        Tbl.Col.value('@City', 'NVARCHAR(100)'),
        Tbl.Col.value('@State', 'NVARCHAR(100)'),
        Tbl.Col.value('@PostalCode', 'NVARCHAR(100)'),
        Tbl.Col.value('@CountryCode', 'NVARCHAR(100)')
    FROM        
        @xml.nodes('//LicensingReportProcessResult/LicensingReport/Address') Tbl(Col)

-- verify results
SELECT * FROM #Address

I want to insert different element data into separate tables. Like Address data into an Address table and Person data into a Person table. As new elements are added I want to save data into separate tables.

Can someone help?

what database are you using? The solution for MySQL is completely different than the solution for MS-SQL. — Luuk, Jun 25 '21 at 16:55
for MS-SQL, you can do https://stackoverflow.com/questions/3989395/convert-xml-to-table-sql-server, for MySQL: https://stackoverflow.com/questions/5491056/how-to-import-xml-file-into-mysql-database-table-using-xml-load-function — Luuk, Jun 25 '21 at 16:56
While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in SQL. (3) Desired output, based on the sample data in #1 above. (4) Your DBMS vendor and version. — Yitzhak Khabinsky, Jun 25 '21 at 16:58

score 0 · Answer 1 · answered Jun 25 '21 at 19:05

Are you asking how to dynamically define new tables for top level xml elements in a document? You can do that with any Xml Serialization library that reads a document and returns the elements and attributes as a tree, and from that metadata create a table definition that you then execute in sql.

Also consider simply storing your data as xml, perhaps with a defined schema, and then writing queries or views that extract the various elements using XPath or the xml data type methods as you already do, instead of extracting into physical tables.

How to extract multiple xml element into individual tables?

1 Answers1