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

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
ParsmanAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
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
 
Anthony PerkinsCommented:
If you can lose the following namespace, then I have a solution for you:
xmlns="urn:oasis:names:tc:evs:schema:eml"
0
 
Anthony PerkinsCommented:
Or even modify that namespace to:
xmlns:xyz="urn:oasis:names:tc:evs:schema:eml"

Than the solution is trivial.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Anthony PerkinsCommented:
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
 
ParsmanAuthor Commented:
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
 
Anthony PerkinsCommented:
>>stop it from working?<<
It does not.  I gave you a solution.
0
 
ParsmanAuthor Commented:
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
 
Anthony PerkinsCommented:
>>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
 
ParsmanAuthor Commented:
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
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.