Solved

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

Posted on 2006-06-12
9
510 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Styling your websites can become very complex. Here I'll show how SASS can help you better organize, maintain and reuse your CSS code.
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.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

770 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