CODEWITHSUNDEEP
sql-serverxmlbulkinsert
Ezra Polson
Ezra Polson

Reputation: 235

Script to import xml file into SQL Server

I am following an example posted by a member on how to import XML files into SQL Server.

However I have some tags within a tag in my xml file as shown below. For example I am not sure how to reference tags like:

<foreign-keys>
    <key app="EN" db-id="9d2tazta9vxatier92ovpfa9zw9vxvwxtarv">23</key> 
</foreign-keys>

in my SQL script, below is the sample xml file I am dealing with which contains 3 observations

<record>
    <database name="PALEO-1.ENL" path="PALEO-1.ENL">PALEO-1.ENL</database>
    <source-app name="EndNote" version="17.5">EndNote</source-app>
    <rec-number>23</rec-number>
    <foreign-keys>
         <key app="EN" db-id="9d2tazta9vxatier92ovpfa9zw9vxvwxtarv">23</key>
    </foreign-keys>
    <ref-type name="Equation">39</ref-type>
    <contributors></contributors>
    <titles>
        <title>
            <style face="normal" font="default" size="100%">Boltzmann&apos;s Constant (k)</style>
        </title>
    </titles>
    <dates></dates>
    <image>
        <style face="normal" font="default" size="100%">11982090273675629314boltz.gif</style>
    </image>
    <caption>
         <style face="normal" font="default" size="100%">Boltzmann&apos;s Constant (k)</style>
    </caption>
    <notes>
         <style face="normal" font="default" size="100%">The number that relates the average energy of a molecule to its absolute temperature.  Boltzmann&apos;s constant is approximately 1.38×10</style>
         <style face="superscript" font="default" size="100%">23</style>
         <style face="normal" font="default" size="100%"> J/K (joules/kelvin).</style>
    </notes>
    <urls></urls>
</record>
<record>
<database name="PALEO-1.ENL" path="PALEO-1.ENL">PALEO-1.ENL</database><source-app name="EndNote" version="17.5">EndNote</source-app><rec-number>13</rec-number><foreign-keys><key app="EN" db-id="9d2tazta9vxatier92ovpfa9zw9vxvwxtarv">13</key></foreign-keys><ref-type name="Case">7</ref-type><contributors></contributors><titles><title><style face="normal" font="default" size="100%">Valdez v. Black</style></title><secondary-title><style face="normal" font="default" size="100%">F.2d</style></secondary-title></titles><pages><style face="normal" font="default" size="100%">1071</style></pages><volume><style face="normal" font="default" size="100%">446</style></volume><dates><year><style face="normal" font="default" size="100%">1971</style></year></dates><publisher><style face="normal" font="default" size="100%">10th Circ.</style></publisher><urls></urls></record>
<record>
<database name="PALEO-1.ENL" path="PALEO-1.ENL">PALEO-1.ENL</database><source-app name="EndNote" version="17.5">EndNote</source-app><rec-number>201</rec-number><foreign-keys><key app="EN" db-id="9d2tazta9vxatier92ovpfa9zw9vxvwxtarv">201</key></foreign-keys><ref-type name="Generic">13</ref-type><contributors></contributors><auth-address><style face="normal" font="default" size="100%">American Geophysical Union, 1909 K St. N.W., Washington, DC 20006</style></auth-address><titles><title><style face="normal" font="default" size="100%">Geophysical Research Letters</style></title><secondary-title><style face="normal" font="default" size="100%">Sedimentary Geology</style></secondary-title></titles><num-vols><style face="normal" font="default" size="100%">Vol. 1 (May 1974)-</style></num-vols><keywords><keyword><style face="normal" font="default" size="100%">Geophysics Periodicals</style></keyword><keyword><style face="normal" font="default" size="100%">Planets Periodicals</style></keyword><keyword><style face="normal" font="default" size="100%">Lunar geology Periodicals</style></keyword></keywords><dates><year><style face="normal" font="default" size="100%">1974</style></year></dates><pub-location><style face="normal" font="default" size="100%">Washington, D.C.</style></pub-location><publisher><style face="normal" font="default" size="100%">American Geophysical Union</style></publisher><isbn><style face="normal" font="default" size="100%">0094-8276</style></isbn><call-num><style face="normal" font="default" size="100%">QE500 .G37</style></call-num><notes><style face="normal" font="default" size="100%">Description based on: vol. 13, no. 4, Apr. 1986.&#xD;Semimonthly, 1992-&#xD;Monthly, -1991</style></notes><work-type><style face="normal" font="default" size="100%">serial</style></work-type><urls></urls></record>

Any help on addressing this issue is much appreciated.

Upvotes: 1

Views: 124

Answers (1)

Bistabil
Bistabil

Reputation: 194

Here's an example of how to navigate thru your XML.

SELECT 
    t.record.value('(database/@name)[1]','varchar(100)')
  , t.record.value('(database/@path)[1]','varchar(100)')
  , t.record.value('(foreign-keys/key/@app)[1]','varchar(100)')
  , t.record.query('titles')
  , ca.titles.value('(.)[1]','varchar(100)')
FROM @xml.nodes('record') t(record)
CROSS APPLY t.record.nodes('titles/title') AS ca(titles)

I'm using .value to fetch single values from XML. If you know an element will only appear once per record, like database, you're safe to use it without missing out on any data.

.query will return part of your XML that is found on that location, you can use that to fetch multiple elements in one row.

CROSS APPLY goes further into your records, joins each title with it's parent once, if you had multiple titles per record, you'd get a row for each title.

So you use "@name" to fetch attributes and only "name" to fetch elements.

p.s. I'm not sure about the exact difference between OpenXML and XQuery, but I've always used XQuery.

Upvotes: 1

Related Questions