Solved

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

Posted on 2006-06-12
9
513 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 

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
 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
The viewer will learn how to count occurrences of each item in an array.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

751 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