November 11, 2009 at 11:17 am
Everyone:
I am having a hard time while attempting to obtain a rowset view over an XML document, using OPENXML.
True enough, I do not have much experience with OPENXML... but I have been able to handle a number of different XML documents... so it is possible that I am missing something completely obvious...
Here is the code, with sample XML data (this is the actual XML string I am receiving... just changed a couple of data fields to protect some personal information)...
DECLARE @select_path VARCHAR(255)
DECLARE @xml XML
DECLARE @hDoc INT
select @select_path = '/Resumes/Resume'
SET @xml = '<Resumes xmlns="http://hj.yahooapis.com/v1/schema.rng" xmlns:yahoo="http://www.yahooapis.com/v1/base.rng" xml:lang="en-US" yahoo:start="1" yahoo:total="245" yahoo:count="3"> <Resume yahoo:uri="http://hj.yahooapis.com/v1/Resume/MzQ5NjQxNjI7NjYwMzMzMDU7MTI1Nzk1MTcxNDsxO0RKVEtfamFwdTF3SjVnWEk3NFpGZnctLQ--" xml:lang="en-US"> <id>MzQ5NjQxNjI7NjYwMzMzMDU7MTI1Nzk1MTcxNDsxO0RKVEtfamFwdTF3SjVnWEk3NFpGZnctLQ--</id> <CandidateID>34964162</CandidateID> <CandidateResumeID>11111111</CandidateResumeID> <FirstName>Bruce</FirstName> <LastName>Willis</LastName> <Email>bw@somewhere.com</Email> <City>Kent</City> <State>OH</State> <Country>USA</Country> <HireType>PERM</HireType> <DesiredSalary>30000</DesiredSalary> <Title>PhotoShop Designer</Title> <Experience>10-15</Experience> <WorkAuthCountry>USA</WorkAuthCountry> <LastUpdated>11 Nov 2009</LastUpdated> <Degree>A</Degree> <Duration>FT</Duration> <TravelPreference>0</TravelPreference> <Language Proficiency="Intermediate">us</Language> <Industry>MIS</Industry> </Resume> <Resume yahoo:uri="http://hj.yahooapis.com/v1/Resume/MjYyNDgxNDc7NjU2NTY0Njg7MTI1Nzk1MTcxNDsyO09fcDVFZVhDMG5LM0s0QWN5cXNhLmctLQ--" xml:lang="en-US"> <id>MjYyNDgxNDc7NjU2NTY0Njg7MTI1Nzk1MTcxNDsyO09fcDVFZVhDMG5LM0s0QWN5cXNhLmctLQ--</id> <CandidateID>26248147</CandidateID> <CandidateResumeID>22222222</CandidateResumeID> <FirstName>Giancarlo</FirstName> <LastName>Giannini</LastName> <Email>gg@somewhere.com</Email> <City>Buffalo Grove</City> <State>IL</State> <Country>USA</Country> <HireType>PERM</HireType> <DesiredSalary>80000</DesiredSalary> <Title>Project Manager/Business Analyst (consultant)</Title> <Employer>H20 Plus</Employer> <Experience>15+</Experience> <WorkAuthCountry>USA</WorkAuthCountry> <LastUpdated>11 Nov 2009</LastUpdated> <Degree>BS</Degree> <Duration>FT</Duration> <TravelPreference>25</TravelPreference> <Language Proficiency="Fluent">us</Language> <Industry>MAN</Industry> <Industry>MCO</Industry> </Resume> <Resume yahoo:uri="http://hj.yahooapis.com/v1/Resume/MzMxNzgyODM7NjU5MjA1NDY7MTI1Nzk1MTcxNDsyMztvS1RoTElJVkVvLnJObGFCTHVXc2VnLS0-" xml:lang="en-US"> <id>MzMxNzgyODM7NjU5MjA1NDY7MTI1Nzk1MTcxNDsyMztvS1RoTElJVkVvLnJObGFCTHVXc2VnLS0-</id> <CandidateID>33178283</CandidateID> <CandidateResumeID>333333333</CandidateResumeID> <FirstName>Ornella</FirstName> <LastName>Muti</LastName> <Email>om@somewhere.com</Email> <City>Naperville</City> <State>IL</State> <Country>USA</Country> <HireType>CONT</HireType> <Title>Software Engineer</Title> <Employer>Level3 communications</Employer> <Experience>5-10</Experience> <WorkAuthCountry>USA</WorkAuthCountry> <LastUpdated>04 Nov 2009</LastUpdated> <Degree>MS</Degree> <Duration>FT</Duration> <TravelPreference>0</TravelPreference> <Industry>MIS</Industry> </Resume> </Resumes> <!-- atsweb01.hj.re3.yahoo.com compressed/chunked Wed Nov 11 07:01:59 PST 2009 -->'
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
SELECT *
FROM OPENXML(@hDoc, @select_path, 2)
WITH
(
id VARCHAR(255),
CandidateID VARCHAR(255),
CandidateResumeID VARCHAR(255),
FirstName VARCHAR(255),
LastName VARCHAR(255)
)
EXEC sp_xml_removedocument @hDoc
If I run the code above, my rowset is always empty... what am I doing wrong? I have tried a number of combinations for the Xpath (@select_path)... but I am always coming up with an empty rowset...
Any comments or suggestions are greatly appreciated.
Giorgio
November 11, 2009 at 11:40 am
I'm not sure how openxml can handle typed xml date (meaning xml data using namespaces).
However, since you're using SS2K5 or above I strongly recommend to look into XQuery. It is much more powerful and less resource intensive.
To use the sample data you provided it would look like:
;WITH XMLNAMESPACES (
DEFAULT 'http://hj.yahooapis.com/v1/schema.rng'
)
SELECT
tab.col.value('id[1]','VARCHAR(255)') AS id,
tab.col.value('CandidateID[1]',' VARCHAR(255)') AS CandidateID,
tab.col.value('CandidateResumeID[1]',' VARCHAR(255)') AS CandidateResumeID,
tab.col.value('FirstName[1]',' VARCHAR(255)') AS FirstName,
tab.col.value('LastName[1]',' VARCHAR(255)') AS LastName
from @xml.nodes('Resumes/Resume')AS tab(col)
/* result set
idCandidateIDCandidateResumeIDFirstNameLastName
MzQ5NjQxNjI7NjYwMzMzMDU7MTI1Nzk1MTcxNDsxO0RKVEtfamFwdTF3SjVnWEk3NFpGZnctLQ--3496416211111111BruceWillis
MjYyNDgxNDc7NjU2NTY0Njg7MTI1Nzk1MTcxNDsyO09fcDVFZVhDMG5LM0s0QWN5cXNhLmctLQ--2624814722222222GiancarloGiannini
MzMxNzgyODM7NjU5MjA1NDY7MTI1Nzk1MTcxNDsyMztvS1RoTElJVkVvLnJObGFCTHVXc2VnLS0-33178283333333333OrnellaMuti
*/
November 11, 2009 at 12:21 pm
That was such a fast reply... thank you so much. The solution you propose is, indeed, just what I needed. I am only now starting to work with XML and am slowly learning... this opens up a whole new subject for me to study.
Thank you again for your help.
Giorgio
November 11, 2009 at 12:47 pm
Glad I could help 🙂
When learning XQuery you should consider looking into the great series of articles by Jacob Sebastian on this site. Search for "XML Workshop".
His series covers a lot of issues when dealing with xml data (e.g. generate xml output from a relational table, work with xml data, like import, modify a.s.o.).
Great articles, if you ask me...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply