Solved

Using OpenXML to insert part of an xml file into a database where subelelements names are complex.

Posted on 2006-06-12
9
504 Views
Last Modified: 2013-11-19
Hi,
I'm new to using Openxml to insert XML file data in to a SQL server. I'm trying to insert mulitple XML files into our database but I'm finding it hard to select specific elements only. for now I just need to select the sub elements of all occurances of the <Voter> element in each XML file. I'm not sure how to handle elements that do not have "strightforward" names (e.g. <firstname>) such as the <pd:PersonNameTitle> in the example below. I would appreciate any help in explaining what kind of Xpath statement should be used in the SELECT clause along with the related WITH clause?

declare @id int

declare @ThexmlData varchar(2000)

set @ThexmlData = '<?xml version="1.0"?>
<EML xmlns:apd="http://www.govtalk.gov.uk/people/AddressAndPersonalDetails" xmlns:bs7666="http://www.govtalk.gov.uk/people/bs7666" xmlns:core="http://www.govtalk.gov.uk/ukcore" xmlns:pd="http://www.govtalk.gov.uk/ukcore#pd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" Id="330-9" SchemaVersion="4.0" xsi:schemaLocation="urn:oasis:names:tc:evs:schema:eml ..\Schemas\330-electionlist-v4-0.xsd http://www.govtalk.gov.uk/ukcore ..\Schemas\UKCORE-v0-2.xsd http://www.govtalk.gov.uk/ukcore#pd testAutoSchema.xsd" xmlns="urn:oasis:names:tc:evs:schema:eml">
  <TransactionId>44444</TransactionId>
  <SequenceNumber>4</SequenceNumber>
  <NumberInSequence>44</NumberInSequence>
  <SequencedElementName>VoterDetails</SequencedElementName>
  <core:SchematronLocation>..\Schematron\330-T9-v1-0.sch</core:SchematronLocation>
  <ElectionList>
    <VoterDetails>
      <VoterRegistration>
        <Voter>
          <VoterIdentification>
            <VoterName>
              <pd:PersonNameTitle>Mr</pd:PersonNameTitle>
              <pd:PersonGivenName>Joe</pd:PersonGivenName>
              <pd:PersonFamilyName>Blogs</pd:PersonFamilyName>
            </VoterName>
            <ElectoralAddress>
              <apd:BS7666Address>
                <bs7666:PAON>
                  <bs7666:StartRange>
                    <bs7666:Number>4</bs7666:Number>
                  </bs7666:StartRange>
                </bs7666:PAON>
                <bs7666:StreetDescription>What Ever Place</bs7666:StreetDescription>
                <bs7666:Locality>Around Road</bs7666:Locality>
                <bs7666:Town>Nonville</bs7666:Town>
                <bs7666:AdministrativeArea>Villiton</bs7666:AdministrativeArea>
                <bs7666:PostCode>XX XXX</bs7666:PostCode>
              </apd:BS7666Address>
              <apd:WalkSort>44444</apd:WalkSort>
            </ElectoralAddress>
            <Id xsi:type="core:PersonNumberType">44444</Id>
          </VoterIdentification>
          <VoterInformation>
            <PollingDistrict Id="AK"/>
          </VoterInformation>
        </Voter>
      </VoterRegistration>
    </VoterDetails>
   </ElectionList>
</EML>'

exec sp_xml_preparedocument @id output, @ThexmlData

select * from OpenXML(@id, '//VoterName/', 2)
with (Voter varchar(50),)


exec sp_xml_removedocument @id

Thx in advance
Pars
0
Comment
Question by:Parsman
  • 6
  • 3
9 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16886908
If you can lose the following namespace, then I have a solution for you:
xmlns="urn:oasis:names:tc:evs:schema:eml"
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16886999
Or even modify that namespace to:
xmlns:xyz="urn:oasis:names:tc:evs:schema:eml"

Than the solution is trivial.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16887086
Otherwise this will do the trick:

declare @id int

declare @ThexmlData varchar(8000),
      @XPath varchar(200)

set @ThexmlData =
'<?xml version="1.0"?>
<EML
      xmlns:apd="http://www.govtalk.gov.uk/people/AddressAndPersonalDetails"
       xmlns:bs7666="http://www.govtalk.gov.uk/people/bs7666"
       xmlns:core="http://www.govtalk.gov.uk/ukcore"
       xmlns:pd="http://www.govtalk.gov.uk/ukcore#pd"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" Id="330-9" SchemaVersion="4.0"
       xsi:schemaLocation="urn:oasis:names:tc:evs:schema:eml ..\Schemas\330-electionlist-v4-0.xsd http://www.govtalk.gov.uk/ukcore ..\Schemas\UKCORE-v0-2.xsd http://www.govtalk.gov.uk/ukcore#pd testAutoSchema.xsd"
      xmlns="urn:oasis:names:tc:evs:schema:eml">
  <TransactionId>44444</TransactionId>
  <SequenceNumber>4</SequenceNumber>
  <NumberInSequence>44</NumberInSequence>
  <SequencedElementName>VoterDetails</SequencedElementName>
  <core:SchematronLocation>..\Schematron\330-T9-v1-0.sch</core:SchematronLocation>
  <ElectionList>
    <VoterDetails>
      <VoterRegistration>
        <Voter>
          <VoterIdentification>
            <VoterName>
              <pd:PersonNameTitle>Mr</pd:PersonNameTitle>
              <pd:PersonGivenName>Joe</pd:PersonGivenName>
              <pd:PersonFamilyName>Blogs</pd:PersonFamilyName>
            </VoterName>
            <ElectoralAddress>
              <apd:BS7666Address>
                <bs7666:PAON>
                  <bs7666:StartRange>
                    <bs7666:Number>4</bs7666:Number>
                  </bs7666:StartRange>
                </bs7666:PAON>
                <bs7666:StreetDescription>What Ever Place</bs7666:StreetDescription>
                <bs7666:Locality>Around Road</bs7666:Locality>
                <bs7666:Town>Nonville</bs7666:Town>
                <bs7666:AdministrativeArea>Villiton</bs7666:AdministrativeArea>
                <bs7666:PostCode>XX XXX</bs7666:PostCode>
              </apd:BS7666Address>
              <apd:WalkSort>44444</apd:WalkSort>
            </ElectoralAddress>
            <Id xsi:type="core:PersonNumberType">44444</Id>
          </VoterIdentification>
          <VoterInformation>
            <PollingDistrict Id="AK"/>
          </VoterInformation>
        </Voter>
      </VoterRegistration>
    </VoterDetails>
   </ElectionList>
</EML>'

exec sp_xml_preparedocument @id output, @ThexmlData,
       '<ns       xmlns:apd="http://www.govtalk.gov.uk/people/AddressAndPersonalDetails"
              xmlns:bs7666="http://www.govtalk.gov.uk/people/bs7666"
              xmlns:core="http://www.govtalk.gov.uk/ukcore"
              xmlns:pd="http://www.govtalk.gov.uk/ukcore#pd"
              xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" Id="330-9" SchemaVersion="4.0"
            xmlns:xyz="urn:oasis:names:tc:evs:schema:eml"/>'

Set @XPath = 'xyz:EML/xyz:ElectionList/xyz:VoterDetails/xyz:VoterRegistration/xyz:Voter/xyz:VoterIdentification/xyz:VoterName'

select      PersonNameTitle,
      PersonGivenName,
      PersonFamilyName
from OpenXML(@id, @XPath, 2) With (
      PersonNameTitle varchar(50) 'pd:PersonNameTitle',
       PersonGivenName varchar(50) 'pd:PersonGivenName',
       PersonFamilyName varchar(50) 'pd:PersonFamilyName')


exec sp_xml_removedocument @id
0
 

Author Comment

by:Parsman
ID: 16892430
Thanks for the help, this is all new to me, why does the namespace xmlns="urn:oasis:names:tc:evs:schema:eml" stop it from working?

Pars.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16893874
>>stop it from working?<<
It does not.  I gave you a solution.
0
 

Author Comment

by:Parsman
ID: 16894904
Read your reponse incorrectly.
I've put the code into a procedure but when I run it in analyser it throws an error message:


"Server: Msg 2714, Level 16, State 5, Procedure proc_Xml_TransferTest, Line 36
There is already an object named 'proc_Xml_TransferTest' in the database.


CREATE Procedure proc_Xml_TransferTest
          @TheXmlData text,
          @xml_Namespace varchar(8000),
          @XPath varchar(800)

As

DECLARE @hDoc int

SET NOCOUNT ON

EXEC sp_xml_preparedocument @hDoc OUTPUT,@TheXmlData, @xml_Namespace


select     PersonNameTitle,
     PersonGivenName,
     PersonFamilyName,
     Number,
     StreetDescription,
     Locality,
     Town,
     PostCode
     
from OpenXML(@hDoc, @XPath, 2) With (
     PersonNameTitle varchar(50) 'pd:PersonNameTitle',
      PersonGivenName varchar(50) 'pd:PersonGivenName',
      PersonFamilyName varchar(50) 'pd:PersonFamilyName',
      Number varchar(10) '../xyz:ElectoralAddress/apd:BS7666Address/bs7666:PAON/bs7666:StartRange/bs7666:Number',
      StreetDescription varchar(100) '../xyz:ElectoralAddress/apd:BS7666Address/bs7666:StreetDescription',
      Locality varchar(100) '../xyz:ElectoralAddress/apd:BS7666Address/bs7666:Locality',
      Town varchar (100) '../xyz:ElectoralAddress/apd:BS7666Address/bs7666:Town',
      PostCode varchar (100) '../xyz:ElectoralAddress/apd:BS7666Address/bs7666:PostCode'
     )


EXEC sp_xml_removedocument @hDoc
GO


proc_Xml_TransferTest
'<?xml version="1.0" encoding="UTF-8"?>
<EML xmlns:apd="http://www.govtalk.gov.uk/people/AddressAndPersonalDetails" xmlns:bs7666="http://www.govtalk.gov.uk/people/bs7666" xmlns:core="http://www.govtalk.gov.uk/ukcore" xmlns:pd="http://www.govtalk.gov.uk/ukcore#pd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" Id="330-9" SchemaVersion="4.0" xsi:schemaLocation="urn:oasis:names:tc:evs:schema:eml ..\Schemas\330-electionlist-v4-0.xsd http://www.govtalk.gov.uk/ukcore ..\Schemas\UKCORE-v0-2.xsd http://www.govtalk.gov.uk/ukcore#pd testAutoSchema.xsd" xmlns="urn:oasis:names:tc:evs:schema:eml">
  <TransactionId>44444</TransactionId>
  <SequenceNumber>4</SequenceNumber>
  <NumberInSequence>44</NumberInSequence>
  <SequencedElementName>VoterDetails</SequencedElementName>
  <core:SchematronLocation>..\Schematron\330-T9-v1-0.sch</core:SchematronLocation>
  <ElectionList>
    <VoterDetails>
      <VoterRegistration>
        <Voter>
          <VoterIdentification>
            <VoterName>
              <pd:PersonNameTitle>Mr</pd:PersonNameTitle>
              <pd:PersonGivenName>Joe</pd:PersonGivenName>
              <pd:PersonFamilyName>Blogs</pd:PersonFamilyName>
            </VoterName>
            <ElectoralAddress>
              <apd:BS7666Address>
                <bs7666:PAON>
                  <bs7666:StartRange>
                    <bs7666:Number>4</bs7666:Number>
                  </bs7666:StartRange>
                </bs7666:PAON>
                <bs7666:StreetDescription>What Ever Place</bs7666:StreetDescription>
                <bs7666:Locality>Around Road</bs7666:Locality>
                <bs7666:Town>Nonville</bs7666:Town>
                <bs7666:AdministrativeArea>Villiton</bs7666:AdministrativeArea>
                <bs7666:PostCode>XX XXX</bs7666:PostCode>
              </apd:BS7666Address>
              <apd:WalkSort>44444</apd:WalkSort>
            </ElectoralAddress>
            <Id xsi:type="core:PersonNumberType">44444</Id>
          </VoterIdentification>
          <VoterInformation>
            <PollingDistrict Id="AK"/>
          </VoterInformation>
        </Voter>
      </VoterRegistration>
    </VoterDetails>
   </ElectionList>
</EML>',
'<ns      xmlns:apd="http://www.govtalk.gov.uk/people/AddressAndPersonalDetails"
            xmlns:bs7666="http://www.govtalk.gov.uk/people/bs7666"
            xmlns:core="http://www.govtalk.gov.uk/ukcore"
            xmlns:pd="http://www.govtalk.gov.uk/ukcore#pd"
            xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" Id="330-9" SchemaVersion="4.0"
          xmlns:xyz="urn:oasis:names:tc:evs:schema:eml"/>',
'xyz:EML/xyz:ElectionList/xyz:VoterDetails/xyz:VoterRegistration/xyz:Voter/xyz:VoterIdentification/xyz:VoterName'

Why is this the case? I have to keep on changing the name.
Basically the end goal is to Iteratively read each  XML file  stored on the C drive and insert a record for each one into the database.
I was planning to call the procedure inside a loop in a front end script (coldfusion)  passing in each xml file contents as a string text variable.

Is their a way to  read each  XML file stored on a local drive and insert (display) the results in SQL server using stored procedure / vbscript (openxml)  given the above example, where I require the ability to select only the elements I require?

Pars

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16896019
>>Why is this the case? I have to keep on changing the name.<<
That would be because you are using CREATE and not ALTER (read up in Books Online).  In other words change:

CREATE Procedure proc_Xml_TransferTest

To:
ALTER Procedure proc_Xml_TransferTest

>>I was planning to call the procedure inside a loop in a front end script (coldfusion)  passing in each xml file contents as a string text variable.<<
This is the correct way to do it.
0
 

Author Comment

by:Parsman
ID: 16900995
Thanks again for the help, I'm more of a Coldfusion developer and just started using T-SQL and xml data parsing  so all this is new to me.

Pars
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 125 total points
ID: 16906949
No problem.  If you feel this question is now answered, please go ahead and close it.  See here from the EE Help:

What are my choices?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi67
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now