Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

XML Level/Aliasing Help Expand / Collapse
Author
Message
Posted Monday, September 30, 2013 11:34 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 445, Visits: 1,800
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



  Post Attachments 
Visit_Survey_2013_08_16_07_21_43_edit.txt (2 views, 7.32 KB)
Post #1500129
Posted Monday, September 30, 2013 12:43 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 6:42 AM
Points: 592, Visits: 2,765
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




-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1500162
Posted Monday, September 30, 2013 1:31 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 445, Visits: 1,800
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!
Post #1500178
Posted Monday, September 30, 2013 1:59 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 445, Visits: 1,800
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

Post #1500187
Posted Monday, September 30, 2013 4:58 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 6:42 AM
Points: 592, Visits: 2,765
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".




-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1500220
Posted Monday, September 30, 2013 6:21 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 445, Visits: 1,800
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.
Post #1500233
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse