Solved

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

Posted on 2006-06-12
9
506 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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 …

895 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

13 Experts available now in Live!

Get 1:1 Help Now