June 17, 2016 at 1:30 am
Hi,
I need to get data from an XML file and inserted in a table in ms sql. XML file is changed and now I simply can´t extract data. So hope for help here 🙂
This is my XML file in short version.
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<export xmlns="http://eu.europa.ec/fpi/fsd/export" generationDate="2016-05-30T19:50:05.779+02:00" globalFileId="108740">
<sanctionEntity designationDetails="" unitedNationId="" logicalId="1">
<regulation regulationType="amendment" organisationType="commission" publicationDate="2016-02-17" entryIntoForceDate="2016-02-18" numberTitle="2016/218 (OJ L40)" programme="ZWE" logicalId="92906">
<publicationUrl>http://eur-lex.europa.eu/legal-content/EN/TXT/PDF/?uri=CELEX:32016R0218&from=EN</publicationUrl>
</regulation>
<subjectType code="person" classificationCode="P"/>
<nameAlias firstName="Robert" middleName="Gabriel" lastName="Mugabe" wholeName="Robert Gabriel Mugabe" function="President" gender="M" title="" nameLanguage="" strong="true" regulationLanguage="en" logicalId="1">
<regulationSummary regulationType="amendment" publicationDate="2005-06-16" numberTitle="898/2005 (OJ L153)" publicationUrl="http://eur-lex.europa.eu/LexUriServ/LexUriServ.do?uri=OJ:L:2005:153:0009:0014:EN:PDF"/>
</nameAlias>
<birthdate circa="false" calendarType="GREGORIAN" city="" zipCode="" birthdate="1924-02-21" dayOfMonth="21" monthOfYear="2" year="1924" region="" place="" countryIso2Code="00" countryDescription="UNKNOWN" regulationLanguage="en" logicalId="1">
<regulationSummary regulationType="amendment" publicationDate="2005-06-16" numberTitle="898/2005 (OJ L153)" publicationUrl="http://eur-lex.europa.eu/LexUriServ/LexUriServ.do?uri=OJ:L:2005:153:0009:0014:EN:PDF"/>
</birthdate>
<identification diplomatic="false" knownExpired="false" knownFalse="false" reportedLost="false" revokedByIssuer="false" issuedBy="" latinNumber="" nameOnDocument="" number="AD001095" region="" countryIso2Code="00" countryDescription="UNKNOWN" identificationTypeCode="passport" identificationTypeDescription="National passport" regulationLanguage="en" logicalId="315">
<remark>(passport)</remark>
<regulationSummary regulationType="amendment" publicationDate="2012-02-22" numberTitle="151/2012 (OJ L49)" publicationUrl="http://eur-lex.europa.eu/LexUriServ/LexUriServ.do?uri=OJ:L:2012:049:0002:0016:EN:PDF"/>
</identification>
</sanctionEntity>
</export>
This is the script I run in SSMS:
DECLARE @x xml
SELECT @x = T
FROM OPENROWSET (BULK 'C:\temp\01-06-2016Copy.xml', SINGLE_BLOB) AS Terrorliste(T)
DECLARE @hdoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @x
SELECT *
FROM OPENXML (@hdoc, '/export/sanctionEntity/nameAlias', 2)
WITH (
firstName varchar(200),
middlenName varchar(200),
lastName varchar(200),
wholeName varchar(400),
logicalId varchar(10))
EXEC sp_xml_removedocument @hdoc
Hope someone can help me.
Thanks 🙂
June 17, 2016 at 9:51 am
Make sure the xml is well-formed. I think you should look at your url statements first. Also look at the ampersand in one of the urls.
You might also have a problem with the namespace declaration in the export section.
There's also a spelling mistake in the fields you want to read (in the select * from openxml).
June 20, 2016 at 12:40 am
Thanks for answer.
Well, Spelling should be in place now, but no change i result.
DECLARE @x xml
SELECT @x = T
FROM OPENROWSET (BULK 'C:\temp\01-06-2016Copy.xml', SINGLE_BLOB) AS Terrorliste(T)
DECLARE @hdoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @x
SELECT *
FROM OPENXML (@hdoc, '/export/sanctionEntity/nameAlias', 2)
WITH (
firstName varchar(200),
middleName varchar(200),
lastName varchar(200),
wholeName varchar(400),
logicalId varchar(10))
EXEC sp_xml_removedocument @hdoc
The XML file is downloaded from the European Union website - http://eeas.europa.eu/cfsp/sanctions/consol-list/index_en.htm
The second XML file link under "1. Conslidated list" - this is the new format - http://ec.europa.eu/external_relations/cfsp/sanctions/list/version4/global/global.fsd.xml
I can read the first XML file under "1. Conslidated list" using my script, with change in FROM OPENXML of course - http://ec.europa.eu/external_relations/cfsp/sanctions/list/version4/global/global.xml
I need to use the new format (FSD format).
Thanks for help 😉
June 20, 2016 at 4:30 am
The changes are
1) They've added a namespace declaration
2) Attributes and elements have been altered a bit
To solve 2, remove the namespace in the xml file (just for testing) and change your 'select' statement so it looks like this:
firstName varchar(200) '@firstName',
middleName varchar(200) '@middleName',
lastName varchar(200) '@lastName',
wholeName varchar(400) '@wholeName',
logicalId varchar(10) '@logicalId'
That should start to get you data.
To solve 1) you need to alter the 'select' statement to tell it about the namespace. I'll leave that for you to solve.
June 20, 2016 at 6:18 am
I have to throw in the towel.
Perhaps you can help me all the way. Have used google but find nothing useful.
In advance, thank you for your help 🙂
June 20, 2016 at 6:34 am
I'll help but you have to show some effort.
Did you follow my suggestions in the last post? If you just need the data on a one-off basis, then that will work.
If you need the full solution, get that part working first. Then, put the namespace declaration back into the file, and read up on how to query using openxml (with a namespace)
- and logicalID should be an int
June 21, 2016 at 7:39 am
So, now I can read the XML file 🙂
I removed the namespace and changed the sql script.
DECLARE @x xml
SELECT @x = T
FROM OPENROWSET (BULK 'C:\temp\01-06-2016.xml', SINGLE_BLOB) AS Terrorliste(T)
DECLARE @hdoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @x
SELECT *
FROM OPENXML (@hdoc, '/export/sanctionEntity/nameAlias', 2)
WITH (
firstName varchar(200)'@firstName',
middleName varchar(200)'@middleName',
lastName varchar(200)'@lastName',
wholeName varchar(400)'@wholeName',
logicalId varchar(10)'../@logicalId')
EXEC sp_xml_removedocument @hdoc
I will try to find som solutions on how to use namespace in OPENXML.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply