September 30, 2013 at 11:34 am
Hi,
I have this XML file. It's my first time trying to load one. It has three levels, and for some reason when I set the aliases up to read back to the appropriate level, I'm getting NULLs. When I just read the file normally level by level, I can use three queries to get the data sorted out properly. Of course, I'd rather not read the file three times and then join the data to insert. Can anyone point me in the right direction? Code is below and dummy data XML file is attached.
Thanks
DECLARE @x xml
SELECT @x = P
FROM OPENROWSET (BULK '\\usd\SurveyComputing\Sample\SampleRepository\Visit_Survey_2013_08_16_07_21_43.XML', SINGLE_BLOB) AS FMG(P)
DECLARE @hdoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @x
select *
from OPENXML (@hdoc, 'SURVEY_EXTRACT/VISIT_SURVEY/SURVEY_CNTCTS/SURVEY_CNTCTS_ROLE', 2)
WITH (SURVEY_ID int '../../@SURVEY_ID',
SURVEY_TYPE nvarchar(128) '../../@SURVEY_TYPE',
ENGLOC_ID int '../../@ENGLOC_ID',
INDEX_NUM nvarchar(128) '../../@INDEX_NUM',
RECORD_NUM nvarchar(128) '../../@RECORD_NUM',
FM_AFM nvarchar(128) '../../@FM_AFM',
FINAL_CONFERENCE_DATE datetime '../../@FINAL_CONFERENCE_DATE',
VISIT_TYPE nvarchar(128) '../../@VISIT_TYPE',
SERVICE_TYPE nvarchar(128) '../../@SERVICE_TYPE',
LANGUAGE nvarchar(128) '../../@LANGUAGE',
LOCATION_ADDRESS_1 nvarchar(128) '../../@LOCATION_ADDRESS_1',
LOCATION_ADDRESS_2 nvarchar(128) '../../@LOCATION_ADDRESS_2',
LOCATION_CITY nvarchar(128) '../../@LOCATION_CITY',
LOCATION_STATE_PROVINCE nvarchar(128) '../../@LOCATION_STATE_PROVINCE',
LOCATION_POSTAL_CODE nvarchar(128) '../../@LOCATION_POSTAL_CODE',
LOCATION_COUNTRY nvarchar(128) '../../@LOCATION_COUNTRY',
ACCOUNT_NUM nvarchar(128) '../../@ACCOUNT_NUM',
ACCOUNT_NAME nvarchar(128) '../../@ACCOUNT_NAME',
AE_INSURANCE_OFFICE nvarchar(128) '../../@AE_INSURANCE_OFFICE',
FE_SERVICING_OFFICE nvarchar(128) '../../@FE_SERVICING_OFFICE',
AE_NAME nvarchar(128) '../../@AE_NAME',
AE_EMAIL nvarchar(128) '../../@AE_EMAIL',
FE_NAME nvarchar(128) '../../@FE_NAME',
VISIT_SURVEY_DATE nvarchar(128) '../../@VISIT_SURVEY_DATE',
SURVEY_CNTCTS nvarchar(128) '../@SURVEY_CNTCTS',
SURVEY_CONTACT_ID int '../@SURVEY_CONTACT_ID',
FIRST_NAME nvarchar(128) '../@FIRST_NAME',
LAST_NAME nvarchar(128) '../@LAST_NAME',
MIDDLE_INITIAL nvarchar(128) '../@MIDDLE_INITIAL',
PREFIX nvarchar(128) '../@PREFIX',
SUFFIX nvarchar(128) '../@SUFFIX',
PROFESSIONAL_TITLE nvarchar(128) '../@PROFESSIONAL_TITLE',
EMAIL nvarchar(128) '../@EMAIL',
PHONE nvarchar(128) '../@PHONE',
SURVEY_CNTCTS_ROLE nvarchar(128),
SURVEY_CONTACT_ROLE_ID nvarchar(128),
SURVEY_CONTACT_ROLE nvarchar(128),
VISIT_SURVEY nvarchar(128),
SURVEY_EXTRACT nvarchar(128)
)
exec sp_xml_removedocument @hdoc
September 30, 2013 at 12:43 pm
This is what you are looking for.
DECLARE @x xml;
SELECT @x = P
FROM OPENROWSET (BULK '\\usd\SurveyComputing\Sample\SampleRepository\Visit_Survey_2013_08_16_07_21_43.XML', SINGLE_BLOB) AS FMG(P)
DECLARE @hdoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @x
select *
from OPENXML (@hdoc, 'SURVEY_EXTRACT/VISIT_SURVEY', 2)
WITH
(
SURVEY_ID int,
SURVEY_TYPE nvarchar(128),
ENGLOC_ID int,
INDEX_NUM nvarchar(128),
RECORD_NUM nvarchar(128),
FM_MMM nvarchar(128),
FINAL_CONFERENCE_DATE datetime,
VISIT_TYPE nvarchar(128),
SERVICE_TYPE nvarchar(128),
[LANGUAGE] nvarchar(128),
LOCATION_ADDRESS_1 nvarchar(128),
LOCATION_ADDRESS_2 nvarchar(128),
LOCATION_CITY nvarchar(128),
LOCATION_STATE_PROVINCE nvarchar(128),
LOCATION_POSTAL_CODE nvarchar(128),
LOCATION_COUNTRY nvarchar(128),
ACCOUNT_NUM nvarchar(128),
ACCOUNT_NAME nvarchar(128),
AE_INSURANCE_OFFICE nvarchar(128),
FE_SERVICING_OFFICE nvarchar(128),
AE_NAME nvarchar(128),
AE_EMAIL nvarchar(128),
FE_NAME nvarchar(128),
VISIT_SURVEY_DATE nvarchar(128),
SURVEY_CNTCTS nvarchar(128),
SURVEY_CONTACT_ID int 'SURVEY_CNTCTS/SURVEY_CONTACT_ID',
FIRST_NAME nvarchar(128) 'SURVEY_CNTCTS/FIRST_NAME',
LAST_NAME nvarchar(128) 'SURVEY_CNTCTS/LAST_NAME',
MIDDLE_INITIAL nvarchar(128) 'SURVEY_CNTCTS/MIDDLE_INITIAL',
PREFIX nvarchar(128) 'SURVEY_CNTCTS/PREFIX',
SUFFIX nvarchar(128) 'SURVEY_CNTCTS/SUFFIX',
PROFESSIONAL_TITLE nvarchar(128) 'SURVEY_CNTCTS/PROFESSIONAL_TITLE',
EMAIL nvarchar(128) 'SURVEY_CNTCTS/EMAIL',
PHONE nvarchar(128) 'SURVEY_CNTCTS/PHONE',
SURVEY_CNTCTS_ROLE nvarchar(128) 'SURVEY_CNTCTS/SURVEY_CNTCTS_ROLE',
SURVEY_CONTACT_ROLE_ID nvarchar(128) 'SURVEY_CNTCTS/SURVEY_CNTCTS_ROLE/SURVEY_CONTACT_ROLE_ID',
SURVEY_CONTACT_ROLE nvarchar(128) 'SURVEY_CNTCTS/SURVEY_CNTCTS_ROLE/SURVEY_CONTACT_ROLE',
VISIT_SURVEY nvarchar(128) '../VISIT_SURVEY',
SURVEY_EXTRACT nvarchar(128) '../../SURVEY_EXTRACT'
)
exec sp_xml_removedocument @hdoc
-- Itzik Ben-Gan 2001
September 30, 2013 at 1:31 pm
Hi Alan,
Thanks, that sort of gets things right. However I still have three columns with concatenated data that should be separated out:
SURVEY_CNTCTS
182142MWilsonm@wilson.com1-514-5623456182142442Senior Management
183144Jean-LucPicardjean-luc@ncc-1701d.fr183144444Senior Management
184146ZMenziesZ.Menzies@thenotebook.com1-601-56589342184146446Facility Hazards Contact184146447Senior Management
185147JimmyKatzjkatz@sandwiches.com1-567-33567788x486185147448Facility Hazards Contact185147449Senior Management
VISIT_SURVEY
182Visit10692810016800002MMM2013-08-13RegularFacility HazardsEnglish (United States)23423 Address StRedmondWashington98053USA024
182Visit10692810016800002MMM2013-08-13RegularFacility HazardsEnglish (United States)23423 Address StRedmondWashington98053USA024
182Visit10692810016800002MMM2013-08-13RegularFacility HazardsEnglish (United States)23423 Address StRedmondWashington98053USA024
182Visit10692810016800002MMM2013-08-13RegularFacility HazardsEnglish (United States)23423 Address StRedmondWashington98053USA024
SURVEY_EXTRACT
182Visit10692810016800002MMM2013-08-13RegularFacility HazardsEnglish (United States)23423 Address StRedmondWashington98053USA024
182Visit10692810016800002MMM2013-08-13RegularFacility HazardsEnglish (United States)23423 Address StRedmondWashington98053USA024
182Visit10692810016800002MMM2013-08-13RegularFacility HazardsEnglish (United States)23423 Address StRedmondWashington98053USA024
182Visit10692810016800002MMM2013-08-13RegularFacility HazardsEnglish (United States)23423 Address StRedmondWashington98053USA024
Any ideas why? Is it because the column names repeat?
Thanks!
September 30, 2013 at 1:59 pm
Actually, this seems to get me where I need to go. For some reason the tutorial I watched had @ signs in alias paths(?). When I took them out, it came back correct.
DECLARE @x xml
SELECT @x = P
FROM OPENROWSET (BULK '\\usd\SurveyComputing\Sample\SampleRepository\Visit_Survey_2013_08_16_07_21_43_edit.XML', SINGLE_BLOB) AS FMG(P)
--select @x
DECLARE @hdoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @x
select *
from OPENXML (@hdoc, 'SURVEY_EXTRACT/VISIT_SURVEY/SURVEY_CNTCTS/SURVEY_CNTCTS_ROLE', 2)
WITH (SURVEY_ID int '../../SURVEY_ID',
SURVEY_TYPE nvarchar(128) '../../SURVEY_TYPE',
ENGLOC_ID int '../../ENGLOC_ID',
INDEX_NUM nvarchar(128) '../../INDEX_NUM',
RECORD_NUM nvarchar(128) '../../RECORD_NUM',
FM_AFM nvarchar(128) '../../FM_AFM',
FINAL_CONFERENCE_DATE datetime '../../FINAL_CONFERENCE_DATE',
VISIT_TYPE nvarchar(128) '../../VISIT_TYPE',
SERVICE_TYPE nvarchar(128) '../../SERVICE_TYPE',
LANGUAGE nvarchar(128) '../../LANGUAGE',
LOCATION_ADDRESS_1 nvarchar(128) '../../LOCATION_ADDRESS_1',
LOCATION_ADDRESS_2 nvarchar(128) '../../LOCATION_ADDRESS_2',
LOCATION_CITY nvarchar(128) '../../LOCATION_CITY',
LOCATION_STATE_PROVINCE nvarchar(128) '../../LOCATION_STATE_PROVINCE',
LOCATION_POSTAL_CODE nvarchar(128) '../../LOCATION_POSTAL_CODE',
LOCATION_COUNTRY nvarchar(128) '../../LOCATION_COUNTRY',
ACCOUNT_NUM nvarchar(128) '../../ACCOUNT_NUM',
ACCOUNT_NAME nvarchar(128) '../../ACCOUNT_NAME',
AE_INSURANCE_OFFICE nvarchar(128) '../../AE_INSURANCE_OFFICE',
FE_SERVICING_OFFICE nvarchar(128) '../../FE_SERVICING_OFFICE',
AE_NAME nvarchar(128) '../../AE_NAME',
AE_EMAIL nvarchar(128) '../../AE_EMAIL',
FE_NAME nvarchar(128) '../../FE_NAME',
VISIT_SURVEY_DATE nvarchar(128) '../../VISIT_SURVEY_DATE',
SURVEY_CNTCTS nvarchar(128) '../SURVEY_CNTCTS',
SURVEY_CONTACT_ID int '../SURVEY_CONTACT_ID',
FIRST_NAME nvarchar(128) '../FIRST_NAME',
LAST_NAME nvarchar(128) '../LAST_NAME',
MIDDLE_INITIAL nvarchar(128) '../MIDDLE_INITIAL',
PREFIX nvarchar(128) '../PREFIX',
SUFFIX nvarchar(128) '../SUFFIX',
PROFESSIONAL_TITLE nvarchar(128) '../PROFESSIONAL_TITLE',
EMAIL nvarchar(128) '../EMAIL',
PHONE nvarchar(128) '../PHONE',
SURVEY_CNTCTS_ROLE nvarchar(128),
SURVEY_CONTACT_ROLE_ID nvarchar(128),
SURVEY_CONTACT_ROLE nvarchar(128),
VISIT_SURVEY nvarchar(128),
SURVEY_EXTRACT nvarchar(128)
)
exec sp_xml_removedocument @hdoc
September 30, 2013 at 4:58 pm
erikd (9/30/2013)
Actually, this seems to get me where I need to go. For some reason the tutorial I watched had @ signs in alias paths(?). When I took them out, it came back correct.
DECLARE @x xml
SELECT @x = P
FROM OPENROWSET (BULK '\\usd\SurveyComputing\Sample\SampleRepository\Visit_Survey_2013_08_16_07_21_43_edit.XML', SINGLE_BLOB) AS FMG(P)
--select @x
DECLARE @hdoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @x
select *
from OPENXML (@hdoc, 'SURVEY_EXTRACT/VISIT_SURVEY/SURVEY_CNTCTS/SURVEY_CNTCTS_ROLE', 2)
WITH (SURVEY_ID int '../../SURVEY_ID',
SURVEY_TYPE nvarchar(128) '../../SURVEY_TYPE',
ENGLOC_ID int '../../ENGLOC_ID',
INDEX_NUM nvarchar(128) '../../INDEX_NUM',
RECORD_NUM nvarchar(128) '../../RECORD_NUM',
FM_AFM nvarchar(128) '../../FM_AFM',
FINAL_CONFERENCE_DATE datetime '../../FINAL_CONFERENCE_DATE',
VISIT_TYPE nvarchar(128) '../../VISIT_TYPE',
SERVICE_TYPE nvarchar(128) '../../SERVICE_TYPE',
LANGUAGE nvarchar(128) '../../LANGUAGE',
LOCATION_ADDRESS_1 nvarchar(128) '../../LOCATION_ADDRESS_1',
LOCATION_ADDRESS_2 nvarchar(128) '../../LOCATION_ADDRESS_2',
LOCATION_CITY nvarchar(128) '../../LOCATION_CITY',
LOCATION_STATE_PROVINCE nvarchar(128) '../../LOCATION_STATE_PROVINCE',
LOCATION_POSTAL_CODE nvarchar(128) '../../LOCATION_POSTAL_CODE',
LOCATION_COUNTRY nvarchar(128) '../../LOCATION_COUNTRY',
ACCOUNT_NUM nvarchar(128) '../../ACCOUNT_NUM',
ACCOUNT_NAME nvarchar(128) '../../ACCOUNT_NAME',
AE_INSURANCE_OFFICE nvarchar(128) '../../AE_INSURANCE_OFFICE',
FE_SERVICING_OFFICE nvarchar(128) '../../FE_SERVICING_OFFICE',
AE_NAME nvarchar(128) '../../AE_NAME',
AE_EMAIL nvarchar(128) '../../AE_EMAIL',
FE_NAME nvarchar(128) '../../FE_NAME',
VISIT_SURVEY_DATE nvarchar(128) '../../VISIT_SURVEY_DATE',
SURVEY_CNTCTS nvarchar(128) '../SURVEY_CNTCTS',
SURVEY_CONTACT_ID int '../SURVEY_CONTACT_ID',
FIRST_NAME nvarchar(128) '../FIRST_NAME',
LAST_NAME nvarchar(128) '../LAST_NAME',
MIDDLE_INITIAL nvarchar(128) '../MIDDLE_INITIAL',
PREFIX nvarchar(128) '../PREFIX',
SUFFIX nvarchar(128) '../SUFFIX',
PROFESSIONAL_TITLE nvarchar(128) '../PROFESSIONAL_TITLE',
EMAIL nvarchar(128) '../EMAIL',
PHONE nvarchar(128) '../PHONE',
SURVEY_CNTCTS_ROLE nvarchar(128),
SURVEY_CONTACT_ROLE_ID nvarchar(128),
SURVEY_CONTACT_ROLE nvarchar(128),
VISIT_SURVEY nvarchar(128),
SURVEY_EXTRACT nvarchar(128)
)
exec sp_xml_removedocument @hdoc
Now I understand exactly what you were trying to do and your solution is good.
To better understand what is going on I suggest you take some time to get familiar with XPATH. The "@" signs represent attributes within an element. For example, take this: <sales sale_id="10">... This is an element named "sales" with an attribute named "sale_id" with a value of "10".
-- Itzik Ben-Gan 2001
September 30, 2013 at 6:21 pm
Yeah, the thing I watched didn't really explain what the @ did, or maybe it would have made more sense if I had a better grasp on the terminology. Either way, thank you again for your help.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply