Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


XML Level/Aliasing Help


XML Level/Aliasing Help

Author
Message
sqldriver
sqldriver
Mr or Mrs. 500
Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)

Group: General Forum Members
Points: 564 Visits: 2492
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


Attachments
Alan.B
Alan.B
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2236 Visits: 7417
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



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
sqldriver
sqldriver
Mr or Mrs. 500
Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)

Group: General Forum Members
Points: 564 Visits: 2492
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!
sqldriver
sqldriver
Mr or Mrs. 500
Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)

Group: General Forum Members
Points: 564 Visits: 2492
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


Alan.B
Alan.B
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2236 Visits: 7417
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



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
sqldriver
sqldriver
Mr or Mrs. 500
Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)

Group: General Forum Members
Points: 564 Visits: 2492
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search