Hi,

I am learning how to parse XML data into SQL table.  Below partial XML data contains multiple sections.  I used OPENXML to parse one section but need to parse the remaining sections into a table.  For example, below openxml code can only retrieve elements under "NewHires/Newhire/EmployeeInfo/PersonName" section.  I also need to parse elements under "NewHires/Newhire/EmployeeInfo/ContactMethod" sections.  Notice that there are three subsections underneath the ContactMethod section ("/Telephone", "/InternatEmailAddress", and "/PostalAddress") sections.  Not to mention there are EmergencyContact section follow behind. 

OpenXML can only extract one section at a time.  Does it mean I have to write multiple OpenXML, store them into multiple temp tables then merge them into a single table?  If that is the case, how to save all these data into a single table?  Any other suggestion to parse all the elements in lieu of OpenXML?

Here is the partial XML data:

<?xml version="1.0" encoding="UTF-8"?>

<style xmlns="http://www.w3.org/1999/xhtml">@namespace html url(http://www.w3.org/1999/xhtml); :root { font:small Verdana; font-weight: bold; padding: 2em; padding- } * { display: block; padding- } html|style { display: none; } html|span, html|a { display: inline; padding: 0; font-weight: normal; text-decoration: none; } html|span.block { display: block; } *[html|hidden], span.block[html|hidden] { display: none; } .expand { display: block; } .expand:before { content: '+'; color: red; } .collapse { display: block; } .collapse:before { content: '-'; color: red; } </style><NewHires recordCount="1" batchDate="4/17/2015 12:30:04 PM" batchId="123456"><newhire xmlns="http://ns.hr-xml.org/2007-04-15"><NewHire xmlns:wta="http://www.ADP.com/wta" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://ns.hr-xml.org/2007-04-15"><typeofhire><TypeOfHire><standardvalue><StandardValue>NewHire</StandardValue></standardvalue></TypeOfHire></typeofhire><employeeinfo><EmployeeInfo><personname><PersonName><givenname><GivenName>applicant</GivenName></givenname><middlename><MiddleName/></middlename><familyname><FamilyName>fourteen</FamilyName></familyname></PersonName></personname><applicantid idowner="ADP"><ApplicantId idOwner="ADP"><idvalue name="candidateId"><IdValue name="candidateId">194018914</IdValue></idvalue></ApplicantId></applicantid><contactmethod><ContactMethod><telephone><Telephone><formattednumber><FormattedNumber>555-555-5555</FormattedNumber></formattednumber></Telephone></telephone><internetemailaddress><InternetEmailAddress>pozzic@xyz.com</InternetEmailAddress></internetemailaddress><postaladdress type="streetAddress"><PostalAddress type="streetAddress"><countrycode><CountryCode>US</CountryCode></countrycode><postalcode><PostalCode>94520</PostalCode></postalcode><region><Region>CA</Region></region><municipality><Municipality>Concord</Municipality></municipality><deliveryaddress><DeliveryAddress><addressline><AddressLine>101 Broadway Road, Suite 100</AddressLine></addressline></DeliveryAddress></deliveryaddress></PostalAddress></postaladdress></ContactMethod></contactmethod><emergencycontact><EmergencyContact><personname><PersonName><formattedname><FormattedName>fdgh dfgd</FormattedName></formattedname><givenname><GivenName>fdgh</GivenName></givenname><familyname><FamilyName>dfgd</FamilyName></familyname></PersonName></personname><contactmethod><ContactMethod><telephone><Telephone><formattednumber><FormattedNumber>555-555-5555</FormattedNumber></formattednumber></Telephone></telephone><postaladdress type="streetAddress"><PostalAddress type="streetAddress"><countrycode><CountryCode>US</CountryCode></countrycode><postalcode><PostalCode>55555</PostalCode></postalcode><region><Region>ca</Region></region><municipality><Municipality>fgh</Municipality></municipality><deliveryaddress><DeliveryAddress><addressline><AddressLine>fdg</AddressLine></addressline></DeliveryAddress></deliveryaddress></PostalAddress></postaladdress></ContactMethod></contactmethod></EmergencyContact></emergencycontact></employeeinfo></newhire>

Below is the SQL statements of OPENXML:<newhire xmlns="http://ns.hr-xml.org/2007-04-15"><employeeinfo><emergencycontact></emergencycontact></employeeinfo></newhire>

DECLARE @x XML
 SELECT @x=N
 FROM OPENROWSET (BULK 'D:\Temp\NewHires.xml', SINGLE_BLOB) as NewHire (N)

DECLARE @hdoc int

EXEC sp_xml_preparedocument @hdoc OUTPUT, @x, '<a:NewHire xmlns:a="http://ns.hr-xml.org/2007-04-15" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:wta="'">http://www.ADP.com/wta"/>'

SELECT *
 FROM OPENXML (@hdoc, '/NewHires/a:NewHire/a:EmployeeInfo/a:PersonName', 2)
 WITH
 (
  BatchDate datetime '../../../@batchDate',
  BatchId int '../../../@batchId',
  GivenName varchar(30) 'a:GivenName',
  FamilyName varchar(30) 'a:FamilyName'

)

EXEC sp_xml_removedocument @hdoc

You XML is in a mess, many end tags missed and it can't be parsed in SQL Server.

Fix it and better post in a better format before getting any advice.

Hi Brian_Ho,

You don't have to write multiple OpenXML, elements are parsed based on the path specified in the OPENXML(the second parameter). See below sample.

declare @xml xml='
<NewHires recordCount="1" batchDate="4/17/2015 12:30:04 PM" batchId="123456">
   <NewHire xmlns="http://ns.hr-xml.org/2007-04-15" xmlns:wta="http://www.ADP.com/wta" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <typeofhire>
         <TypeOfHire>
            <standardvalue>
               <StandardValue>NewHire</StandardValue>
            </standardvalue>
         </TypeOfHire>
      </typeofhire>
      <employeeinfo>
         <EmployeeInfo>
            <personname>
               <PersonName>
                  <givenname>
                     <GivenName>applicant</GivenName>
                  </givenname>
                  <middlename>
                     <MiddleName />
                  </middlename>
                  <familyname>
                     <FamilyName>fourteen</FamilyName>
                  </familyname>
               </PersonName>
            </personname>
            <applicantid idowner="ADP">
               <ApplicantId idOwner="ADP">
                  <idvalue name="candidateId">
                     <IdValue name="candidateId">194018914</IdValue>
                  </idvalue>
               </ApplicantId>
            </applicantid>
         </EmployeeInfo>
      </employeeinfo>
   </NewHire>
</NewHires>
'
select @xml

DECLARE @hdoc int

EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml, '<a:NewHire xmlns:a="http://ns.hr-xml.org/2007-04-15" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:wta="http://www.ADP.com/wta"/>'

--root path
SELECT *
 FROM OPENXML (@hdoc, '/', 2)

SELECT *
 FROM OPENXML (@hdoc, '/NewHires/a:NewHire', 2)
-- WITH
-- (
--  BatchDate datetime '../../../@batchDate',
--  BatchId int '../../../@batchId',
--  GivenName varchar(30) 'a:GivenName',
--  FamilyName varchar(30) 'a:FamilyName'
--)

EXEC sp_xml_removedocument @hdoc

All elements under the specified path are parsed. To get the values in what section, you have to specify the path in the WITH clause.

If you have any question, feel free to let me

Hi Eric,
If I use below root path command, I will get below result which list the elements like below.  If does not give me the column name and the result split into multiple rows.
 
SELECT *
FROM
OPENXML (@hdoc, '/', 2)

id parentid nodetype localname prefix namespaceuri datatype prev text
0 NULL 1 NewHires NULL NULL NULL NULL NULL
2 0 2 batchId NULL NULL NULL NULL NULL
2891 2 3 #text NULL NULL NULL NULL 8569021
3 0 2 batchDate NULL NULL NULL NULL NULL
2892 3 3 #text NULL NULL NULL NULL 4/1/2015 6:34:16 PM
4 0 2 recordCount NULL NULL NULL NULL NULL
2893 4 3 #text NULL NULL NULL NULL 4
5 0 1 NewHire NULL http://ns.hr-xml.org/2007-04-15 NULL NULL NULL
6 5 2 xmlns xmlns NULL NULL NULL NULL
2894 6 3 #text NULL NULL NULL NULL http://ns.hr-xml.org/2007-04-15
7 5 2 xsi xmlns NULL NULL NULL NULL
2895 7 3 #text NULL NULL NULL NULL http://www.w3.org/2001/XMLSchema-instance
8 5 2 wta xmlns NULL NULL NULL NULL
...

Here is a complete XML file that I use for testing.  Please forgive me, it is a long time.

<?xml version="1.0" encoding="utf-8"?><NewHires batchId="8583866" batchDate="4/17/2015 12:30:04 PM" recordCount="1"><NewHire xmlns="http://ns.hr-xml.org/2007-04-15" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:wta="<TypeOfHire><StandardValue>NewHire</StandardValue></TypeOfHire><EmployeeInfo><PersonName><GivenName>applicant</GivenName><MiddleName">http://www.ADP.com/wta"><TypeOfHire><StandardValue>NewHire</StandardValue></TypeOfHire><EmployeeInfo><PersonName><GivenName>applicant</GivenName><MiddleName /><FamilyName>fourteen</FamilyName></PersonName><ApplicantId idOwner="ADP"><IdValue name="candidateId">194018914</IdValue></ApplicantId><ContactMethod><Telephone><FormattedNumber>555-555-5555</FormattedNumber></Telephone><InternetEmailAddress>pozzic@XYZ.com</InternetEmailAddress><PostalAddress type="streetAddress"><CountryCode>US</CountryCode><PostalCode>94520</PostalCode><Region>CA</Region><Municipality>Concord</Municipality><DeliveryAddress><AddressLine>100 Broadway Street Road, Suite 900</AddressLine></DeliveryAddress></PostalAddress></ContactMethod><EmergencyContact><PersonName><FormattedName>fdgh dfgd</FormattedName><GivenName>fdgh</GivenName><FamilyName>dfgd</FamilyName></PersonName><ContactMethod><Telephone><FormattedNumber>555-555-5555</FormattedNumber></Telephone><PostalAddress type="streetAddress"><CountryCode>US</CountryCode><PostalCode>55555</PostalCode><Region>ca</Region><Municipality>fgh</Municipality><DeliveryAddress><AddressLine>fdg</AddressLine></DeliveryAddress></PostalAddress></ContactMethod></EmergencyContact><PersonDescriptors><LegalIdentifiers><PersonLegalId validFrom="notKnown" validTo="notKnown" idOwner="SSA" countryCode="US" jurisdiction="Country" issuingRegion="US" documentType="Social Security Card"><IdValue name="SSN">014000000</IdValue></PersonLegalId></LegalIdentifiers><DemographicDescriptors><Race /><Ethnicity>Yes</Ethnicity></DemographicDescriptors><BiologicalDescriptors><DateOfBirth>1972-04-20</DateOfBirth><GenderCode>2</GenderCode></BiologicalDescriptors></PersonDescriptors></EmployeeInfo><ApplicationInfo><ApplicationHistory><HiringProcessActivity><Type>HireStatusChange</Type><ActivityPerformer><PersonName><GivenName>Casey</GivenName><FamilyName>Pozzi</FamilyName></PersonName><PersonId idOwner="ADP"><IdValue>POZZIC</IdValue></PersonId></ActivityPerformer><Date>2015-04-16</Date></HiringProcessActivity></ApplicationHistory><WorkEligibilityInfo><DocumentId><IdValue>abcdefg</IdValue></DocumentId><DocumentName>U.S. Passport</DocumentName><IssuingAuthority>abcdefg</IssuingAuthority></WorkEligibilityInfo></ApplicationInfo><PositionInfo><ReferenceInfo><PositionId><IdValue name="positionId">110</IdValue></PositionId></ReferenceInfo><OfferInfo><NegotiatedPositionTitle>Sales Associate</NegotiatedPositionTitle><NegotiatedPositionDescription /><EmploymentStartDate>2015-04-16</EmploymentStartDate><RemunerationInfo><BasePay currencyCode="USD" baseInterval="Hourly">10.00</BasePay></RemunerationInfo><ResourceRelationship>Employee</ResourceRelationship></OfferInfo></PositionInfo><UserArea><wta:ADPUserArea><wta:ApplicationData><wta:ApplicationDate>2015-03-20</wta:ApplicationDate><wta:PositionApplied><wta:JobCode>110</wta:JobCode><wta:Title>Sales Associate</wta:Title></wta:PositionApplied><wta:AvailableToStartDate>2015-03-19</wta:AvailableToStartDate><wta:PostalAddresses><wta:PostalAddress type="current"><wta:CountryCode>US</wta:CountryCode><wta:PostalCode>94520</wta:PostalCode><wta:Region>CA</wta:Region><wta:Municipality>Concord</wta:Municipality><wta:DeliveryAddress><wta:AddressLine>100 Broadway Street Road, Suite 900</wta:AddressLine></wta:DeliveryAddress></wta:PostalAddress></wta:PostalAddresses><wta:Application><wta:Name>XYZ Non-Req External Seeker Site</wta:Name><wta:Locator>XYZNonReqExt</wta:Locator></wta:Application><wta:Referral><wta:Source>IJB</wta:Source><wta:SpecificSource>CommunityWebsite</wta:SpecificSource></wta:Referral><wta:Authorization><wta:Disclosures><wta:Disclosure type="FCRA" question="Title"><wta:Question>FAIR CREDIT REPORTING ACT (FCRA) DISCLOSURE AND AUTHORIZATION</wta:Question></wta:Disclosure><wta:Disclosure type="FCRA" question="Authorization"><wta:Question>For employment purposes XYZ! will obtain a report (called a &amp;quot;Consumer Report&amp;quot;) from SterlingBackcheck, 1 State Street Plaza, 24th Floor, New York, NY 10004, 1-800-899-2272, http://www.sterlingbackcheck.com/ (called a &amp;quot;Consumer Reporting Agency&amp;quot;). This report will be used in whole or in part to determine your qualifications and suitability for employment and, to the extent permitted by law, it may include information about your character, general reputation, personal characteristics, mode of living, and credit history. The report could also include motor vehicle or driving record, criminal and civil records, prior employment, education and other public record information. Your authorization will apply pre-employment and, if you are hired, will also apply throughout your employment unless prohibited by law.</wta:Question><wta:Answer>I authorize the procurement of a Consumer Report</wta:Answer></wta:Disclosure><wta:Disclosure type="FCRA" question="SendCopyToApplicant"><wta:Question>For California applicants only: A consumer credit report may be used and, if so, it will be obtained from SterlingBackcheck. An investigative consumer report, as defined under California law, will be obtained for employment purposes, and it may include information about your character, general reputation, personal characteristics, and mode of living. XYZ! may/will request an investigation regarding your credit history, motor vehicle or driving record(s), criminal and civil records, prior employment (including contacting prior employers), education as well as other public record information or information related to your background from SterlingBackcheck. The results of this report may be used as a factor in making employment decisions. Under section 1786.22 of the California Civil Code, you may view the file maintained on you by the consumer reporting agency named above during normal business hours upon reasonable notice. You may also obtain a copy of this file upon submitting proper identification and paying the costs of duplication services, by appearing at the Consumer Reporting Agency identified above in person or by mail. The Consumer Reporting Agency cannot charge you more than the actual copying costs for providing you with a copy of your file. You may also receive a summary of the file by telephone. To do so, you must first make a written request, with proper identification, seeking telephone disclosure and pay for any toll-charges for the call prior to such a call. The agency is required to have personnel available to explain your file to you and the agency must explain to you any coded information appearing in your file. If you appear in person, a person of your choice may accompany you, provided that this person furnishes proper identification. You can also request that a copy of your report be sent to a specific addressee by certified mail. The Consumer Reporting Agency will not be liable for disclosures of your report to any third parties caused by mishandling of such mail after the CRA sends it. &amp;quot;Proper identification&amp;quot; includes documents such as a valid driver&amp;#39;s license, social security account number, military identification card, and credit cards. Only if you cannot identify yourself with such information may the CRA require additional information concerning your employment or personal history in order to verify your identity. You have a right to obtain a free copy of any report obtained by XYZ!. Do you require a mailed copy of the report? For Washington applicants only: A consumer report may be obtained in connection with this application.</wta:Question><wta:Answer>No</wta:Answer></wta:Disclosure></wta:Disclosures><wta:SendCopyToApplicant>No</wta:SendCopyToApplicant></wta:Authorization></wta:ApplicationData><wta:HireData><wta:HireDate>2015-04-16</wta:HireDate><wta:Requestor><wta:PersonName><wta:GivenName>Casey</wta:GivenName><wta:FamilyName>Pozzi</wta:FamilyName></wta:PersonName><wta:Username>POZZIC</wta:Username></wta:Requestor><wta:Location type="store"><wta:StoreNumber>88</wta:StoreNumber><wta:Name>Burbank</wta:Name><wta:PostalAddress><wta:CountryCode>US</wta:CountryCode><wta:PostalCode>91502</wta:PostalCode><wta:Region>CA</wta:Region><wta:Municipality>Burbank</wta:Municipality></wta:PostalAddress><wta:Parent><wta:Location type="district"><wta:StoreNumber>10009</wta:StoreNumber><wta:Name>Richcreek</wta:Name><wta:PostalAddress><wta:CountryCode>US</wta:CountryCode></wta:PostalAddress><wta:Parent><wta:Location type="region"><wta:StoreNumber>902</wta:StoreNumber><wta:Name>Southern California</wta:Name><wta:PostalAddress><wta:CountryCode>US</wta:CountryCode></wta:PostalAddress><wta:Parent><wta:Location type="corporate"><wta:StoreNumber>900</wta:StoreNumber><wta:Name>XYZ!</wta:Name><wta:PostalAddress><wta:CountryCode>US</wta:CountryCode><wta:PostalCode>94520</wta:PostalCode><wta:Region>CA</wta:Region><wta:Municipality>Concord</wta:Municipality></wta:PostalAddress></wta:Location></wta:Parent></wta:Location></wta:Parent></wta:Location></wta:Parent></wta:Location><wta:W4TaxWithholdingInfo><wta:AllowanceA /><wta:AllowanceB /><wta:AllowanceC /><wta:AllowanceD /><wta:AllowanceE /><wta:AllowanceF /><wta:AllowanceG /><wta:AllowanceH /><wta:TotalAllowance>1</wta:TotalAllowance><wta:AdditionalWithholding /><wta:FilingStatus>Married</wta:FilingStatus><wta:IsW4Exempt>No</wta:IsW4Exempt><wta:IsW4LastNameDifferent>false</wta:IsW4LastNameDifferent><wta:ExemptText /></wta:W4TaxWithholdingInfo><wta:I9TaxWithholdingInfo><wta:AlienOrAdmissionNumber /><wta:AlienOrAdmissionType /><wta:MaidenName>dfgdf</wta:MaidenName><wta:BeganEmploymentDate>2015-04-16</wta:BeganEmploymentDate><wta:PreparerName /><wta:PreparerAddress /><wta:EligibilityToWork>USCitizen</wta:EligibilityToWork><wta:ForeignPassportNumber /><wta:IssuanceCountry /><wta:EmailAddress /><wta:PhoneNumber /><wta:IdentityEligibilityListSelection>ListA</wta:IdentityEligibilityListSelection><wta:IdentityEligibilityListA><wta:DocumentType>USPassport</wta:DocumentType><wta:IssuingAuthority>abcdefg</wta:IssuingAuthority><wta:DocumentNumber>abcdefg</wta:DocumentNumber><wta:ReceiptDocument /><wta:SecondDocumentNumber /><wta:ReceiptSecondDocument /><wta:IssuingAuthoritySecond /><wta:ThirdDocumentNumber /><wta:ReceiptThirdDocument /><wta:IssuingAuthorityThird /></wta:IdentityEligibilityListA></wta:I9TaxWithholdingInfo><wta:StateTaxInfo><wta:StateAdditionalTax>1</wta:StateAdditionalTax><wta:StateMaritalStatus>M</wta:StateMaritalStatus><wta:StateAllowances>1</wta:StateAllowances><wta:StateExempt>N</wta:StateExempt></wta:StateTaxInfo><wta:StateTaxComplete><wta:Scripts><wta:Script><wta:ScriptName>State Tax Script California</wta:ScriptName><wta:ScriptQuestion><wta:Question>Filling Status Withholding Allowances.
          </wta:Question><wta:Response>MARRIED(with one income)</wta:Response></wta:ScriptQuestion><wta:ScriptQuestion><wta:Question>Are you subjected to California withholdings?
          </wta:Question><wta:Response>Yes</wta:Response></wta:ScriptQuestion><wta:ScriptQuestion><wta:Question>Please Note: To view this form, close Onboarding. Sign in and then go to the State form page. Click the link to view the form. Keep it open as you answer the questions.
          </wta:Question><wta:Response /></wta:ScriptQuestion><wta:ScriptQuestion><wta:Question>Number of allowances for Regular Withholding Allowances, Worksheet A (Note: The value should be numeric value of no more than 2 digits)
          </wta:Question><wta:Response>1</wta:Response></wta:ScriptQuestion><wta:ScriptQuestion><wta:Question>Number of allowances from the estimated Deductions, Worksheet B (Note: The value should be numeric value of no more than 2 digits)
          </wta:Question><wta:Response>1</wta:Response></wta:ScriptQuestion><wta:ScriptQuestion><wta:Question>Total Number of allowances (A+B) when using the California Withholding Schedules for the current year. (Note: The value should be numeric value of no more than 2 digits)
          </wta:Question><wta:Response>1</wta:Response></wta:ScriptQuestion><wta:ScriptQuestion><wta:Question>Additional amount of State income tax to be withheld each pay period(if employer agrees), Worksheet C (Note: Please enter an additional dollar amount that you wish to have withheld from your paycheck or enter zero)
          </wta:Question><wta:Response>1</wta:Response></wta:ScriptQuestion><wta:ScriptQuestion><wta:Question>Under the penalties of perjury, I certify that the number of withholding allowances claimed on this certificate does not exceed the number to which I am entitled or, if claiming exemption from withholding, that I am entitled to claim the exempt status.
          </wta:Question><wta:Response>Check here to agree</wta:Response></wta:ScriptQuestion></wta:Script></wta:Scripts></wta:StateTaxComplete><wta:PositionKey><wta:Name>Sales Associate 15</wta:Name><wta:Key /></wta:PositionKey><wta:CustomerSpecificValues><wta:CustomerSpecificValue><wta:Category>CustomerSpecific</wta:Category><wta:Name>CLUB_BEV_NUMBER</wta:Name><wta:Value>999</wta:Value></wta:CustomerSpecificValue><wta:CustomerSpecificValue><wta:Category>CustomerSpecific</wta:Category><wta:Name>REPORTS_TO_FIRSTNAME_LASTNAME</wta:Name><wta:Value>Casey pozzi</wta:Value></wta:CustomerSpecificValue></wta:CustomerSpecificValues></wta:HireData></wta:ADPUserArea></UserArea></NewHire></NewHires> 

Here is a complete XML file:

<?xml version="1.0" encoding="utf-8"?><NewHires batchId="8583866" batchDate="4/17/2015 12:30:04 PM" recordCount="1"><NewHire xmlns="http://ns.hr-xml.org/2007-04-15" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:wta="<TypeOfHire><StandardValue>NewHire</StandardValue></TypeOfHire><EmployeeInfo><PersonName><GivenName>applicant</GivenName><MiddleName">http://www.ADP.com/wta"><TypeOfHire><StandardValue>NewHire</StandardValue></TypeOfHire><EmployeeInfo><PersonName><GivenName>applicant</GivenName><MiddleName /><FamilyName>fourteen</FamilyName></PersonName><ApplicantId idOwner="ADP"><IdValue name="candidateId">194018914</IdValue></ApplicantId><ContactMethod><Telephone><FormattedNumber>555-555-5555</FormattedNumber></Telephone><InternetEmailAddress>pozzic@XYZ.com</InternetEmailAddress><PostalAddress type="streetAddress"><CountryCode>US</CountryCode><PostalCode>94520</PostalCode><Region>CA</Region><Municipality>Concord</Municipality><DeliveryAddress><AddressLine>100 Broadway Street Road, Suite 900</AddressLine></DeliveryAddress></PostalAddress></ContactMethod><EmergencyContact><PersonName><FormattedName>fdgh dfgd</FormattedName><GivenName>fdgh</GivenName><FamilyName>dfgd</FamilyName></PersonName><ContactMethod><Telephone><FormattedNumber>555-555-5555</FormattedNumber></Telephone><PostalAddress type="streetAddress"><CountryCode>US</CountryCode><PostalCode>55555</PostalCode><Region>ca</Region><Municipality>fgh</Municipality><DeliveryAddress><AddressLine>fdg</AddressLine></DeliveryAddress></PostalAddress></ContactMethod></EmergencyContact><PersonDescriptors><LegalIdentifiers><PersonLegalId validFrom="notKnown" validTo="notKnown" idOwner="SSA" countryCode="US" jurisdiction="Country" issuingRegion="US" documentType="Social Security Card"><IdValue name="SSN">014000000</IdValue></PersonLegalId></LegalIdentifiers><DemographicDescriptors><Race /><Ethnicity>Yes</Ethnicity></DemographicDescriptors><BiologicalDescriptors><DateOfBirth>1972-04-20</DateOfBirth><GenderCode>2</GenderCode></BiologicalDescriptors></PersonDescriptors></EmployeeInfo><ApplicationInfo><ApplicationHistory><HiringProcessActivity><Type>HireStatusChange</Type><ActivityPerformer><PersonName><GivenName>Casey</GivenName><FamilyName>Pozzi</FamilyName></PersonName><PersonId idOwner="ADP"><IdValue>POZZIC</IdValue></PersonId></ActivityPerformer><Date>2015-04-16</Date></HiringProcessActivity></ApplicationHistory><WorkEligibilityInfo><DocumentId><IdValue>abcdefg</IdValue></DocumentId><DocumentName>U.S. Passport</DocumentName><IssuingAuthority>abcdefg</IssuingAuthority></WorkEligibilityInfo></ApplicationInfo><PositionInfo><ReferenceInfo><PositionId><IdValue name="positionId">110</IdValue></PositionId></ReferenceInfo><OfferInfo><NegotiatedPositionTitle>Sales Associate</NegotiatedPositionTitle><NegotiatedPositionDescription /><EmploymentStartDate>2015-04-16</EmploymentStartDate><RemunerationInfo><BasePay currencyCode="USD" baseInterval="Hourly">10.00</BasePay></RemunerationInfo><ResourceRelationship>Employee</ResourceRelationship></OfferInfo></PositionInfo><UserArea><wta:ADPUserArea><wta:ApplicationData><wta:ApplicationDate>2015-03-20</wta:ApplicationDate><wta:PositionApplied><wta:JobCode>110</wta:JobCode><wta:Title>Sales Associate</wta:Title></wta:PositionApplied><wta:AvailableToStartDate>2015-03-19</wta:AvailableToStartDate><wta:PostalAddresses><wta:PostalAddress type="current"><wta:CountryCode>US</wta:CountryCode><wta:PostalCode>94520</wta:PostalCode><wta:Region>CA</wta:Region><wta:Municipality>Concord</wta:Municipality><wta:DeliveryAddress><wta:AddressLine>100 Broadway Street Road, Suite 900</wta:AddressLine></wta:DeliveryAddress></wta:PostalAddress></wta:PostalAddresses><wta:Application><wta:Name>XYZ Non-Req External Seeker Site</wta:Name><wta:Locator>XYZNonReqExt</wta:Locator></wta:Application><wta:Referral><wta:Source>IJB</wta:Source><wta:SpecificSource>CommunityWebsite</wta:SpecificSource></wta:Referral><wta:Authorization><wta:Disclosures><wta:Disclosure type="FCRA" question="Title"><wta:Question>FAIR CREDIT REPORTING ACT (FCRA) DISCLOSURE AND AUTHORIZATION</wta:Question></wta:Disclosure><wta:Disclosure type="FCRA" question="Authorization"><wta:Question>For employment purposes XYZ! will obtain a report (called a &amp;quot;Consumer Report&amp;quot;) from SterlingBackcheck, 1 State Street Plaza, 24th Floor, New York, NY 10004, 1-800-899-2272, http://www.sterlingbackcheck.com/ (called a &amp;quot;Consumer Reporting Agency&amp;quot;). This report will be used in whole or in part to determine your qualifications and suitability for employment and, to the extent permitted by law, it may include information about your character, general reputation, personal characteristics, mode of living, and credit history. The report could also include motor vehicle or driving record, criminal and civil records, prior employment, education and other public record information. Your authorization will apply pre-employment and, if you are hired, will also apply throughout your employment unless prohibited by law.</wta:Question><wta:Answer>I authorize the procurement of a Consumer Report</wta:Answer></wta:Disclosure><wta:Disclosure type="FCRA" question="SendCopyToApplicant"><wta:Question>For California applicants only: A consumer credit report may be used and, if so, it will be obtained from SterlingBackcheck. An investigative consumer report, as defined under California law, will be obtained for employment purposes, and it may include information about your character, general reputation, personal characteristics, and mode of living. XYZ! may/will request an investigation regarding your credit history, motor vehicle or driving record(s), criminal and civil records, prior employment (including contacting prior employers), education as well as other public record information or information related to your background from SterlingBackcheck. The results of this report may be used as a factor in making employment decisions. Under section 1786.22 of the California Civil Code, you may view the file maintained on you by the consumer reporting agency named above during normal business hours upon reasonable notice. You may also obtain a copy of this file upon submitting proper identification and paying the costs of duplication services, by appearing at the Consumer Reporting Agency identified above in person or by mail. The Consumer Reporting Agency cannot charge you more than the actual copying costs for providing you with a copy of your file. You may also receive a summary of the file by telephone. To do so, you must first make a written request, with proper identification, seeking telephone disclosure and pay for any toll-charges for the call prior to such a call. The agency is required to have personnel available to explain your file to you and the agency must explain to you any coded information appearing in your file. If you appear in person, a person of your choice may accompany you, provided that this person furnishes proper identification. You can also request that a copy of your report be sent to a specific addressee by certified mail. The Consumer Reporting Agency will not be liable for disclosures of your report to any third parties caused by mishandling of such mail after the CRA sends it. &amp;quot;Proper identification&amp;quot; includes documents such as a valid driver&amp;#39;s license, social security account number, military identification card, and credit cards. Only if you cannot identify yourself with such information may the CRA require additional information concerning your employment or personal history in order to verify your identity. You have a right to obtain a free copy of any report obtained by XYZ!. Do you require a mailed copy of the report? For Washington applicants only: A consumer report may be obtained in connection with this application.</wta:Question><wta:Answer>No</wta:Answer></wta:Disclosure></wta:Disclosures><wta:SendCopyToApplicant>No</wta:SendCopyToApplicant></wta:Authorization></wta:ApplicationData><wta:HireData><wta:HireDate>2015-04-16</wta:HireDate><wta:Requestor><wta:PersonName><wta:GivenName>Casey</wta:GivenName><wta:FamilyName>Pozzi</wta:FamilyName></wta:PersonName><wta:Username>POZZIC</wta:Username></wta:Requestor><wta:Location type="store"><wta:StoreNumber>88</wta:StoreNumber><wta:Name>Burbank</wta:Name><wta:PostalAddress><wta:CountryCode>US</wta:CountryCode><wta:PostalCode>91502</wta:PostalCode><wta:Region>CA</wta:Region><wta:Municipality>Burbank</wta:Municipality></wta:PostalAddress><wta:Parent><wta:Location type="district"><wta:StoreNumber>10009</wta:StoreNumber><wta:Name>Richcreek</wta:Name><wta:PostalAddress><wta:CountryCode>US</wta:CountryCode></wta:PostalAddress><wta:Parent><wta:Location type="region"><wta:StoreNumber>902</wta:StoreNumber><wta:Name>Southern California</wta:Name><wta:PostalAddress><wta:CountryCode>US</wta:CountryCode></wta:PostalAddress><wta:Parent><wta:Location type="corporate"><wta:StoreNumber>900</wta:StoreNumber><wta:Name>XYZ!</wta:Name><wta:PostalAddress><wta:CountryCode>US</wta:CountryCode><wta:PostalCode>94520</wta:PostalCode><wta:Region>CA</wta:Region><wta:Municipality>Concord</wta:Municipality></wta:PostalAddress></wta:Location></wta:Parent></wta:Location></wta:Parent></wta:Location></wta:Parent></wta:Location><wta:W4TaxWithholdingInfo><wta:AllowanceA /><wta:AllowanceB /><wta:AllowanceC /><wta:AllowanceD /><wta:AllowanceE /><wta:AllowanceF /><wta:AllowanceG /><wta:AllowanceH /><wta:TotalAllowance>1</wta:TotalAllowance><wta:AdditionalWithholding /><wta:FilingStatus>Married</wta:FilingStatus><wta:IsW4Exempt>No</wta:IsW4Exempt><wta:IsW4LastNameDifferent>false</wta:IsW4LastNameDifferent><wta:ExemptText /></wta:W4TaxWithholdingInfo><wta:I9TaxWithholdingInfo><wta:AlienOrAdmissionNumber /><wta:AlienOrAdmissionType /><wta:MaidenName>dfgdf</wta:MaidenName><wta:BeganEmploymentDate>2015-04-16</wta:BeganEmploymentDate><wta:PreparerName /><wta:PreparerAddress /><wta:EligibilityToWork>USCitizen</wta:EligibilityToWork><wta:ForeignPassportNumber /><wta:IssuanceCountry /><wta:EmailAddress /><wta:PhoneNumber /><wta:IdentityEligibilityListSelection>ListA</wta:IdentityEligibilityListSelection><wta:IdentityEligibilityListA><wta:DocumentType>USPassport</wta:DocumentType><wta:IssuingAuthority>abcdefg</wta:IssuingAuthority><wta:DocumentNumber>abcdefg</wta:DocumentNumber><wta:ReceiptDocument /><wta:SecondDocumentNumber /><wta:ReceiptSecondDocument /><wta:IssuingAuthoritySecond /><wta:ThirdDocumentNumber /><wta:ReceiptThirdDocument /><wta:IssuingAuthorityThird /></wta:IdentityEligibilityListA></wta:I9TaxWithholdingInfo><wta:StateTaxInfo><wta:StateAdditionalTax>1</wta:StateAdditionalTax><wta:StateMaritalStatus>M</wta:StateMaritalStatus><wta:StateAllowances>1</wta:StateAllowances><wta:StateExempt>N</wta:StateExempt></wta:StateTaxInfo><wta:StateTaxComplete><wta:Scripts><wta:Script><wta:ScriptName>State Tax Script California</wta:ScriptName><wta:ScriptQuestion><wta:Question>Filling Status Withholding Allowances.
          </wta:Question><wta:Response>MARRIED(with one income)</wta:Response></wta:ScriptQuestion><wta:ScriptQuestion><wta:Question>Are you subjected to California withholdings?
          </wta:Question><wta:Response>Yes</wta:Response></wta:ScriptQuestion><wta:ScriptQuestion><wta:Question>Please Note: To view this form, close Onboarding. Sign in and then go to the State form page. Click the link to view the form. Keep it open as you answer the questions.
          </wta:Question><wta:Response /></wta:ScriptQuestion><wta:ScriptQuestion><wta:Question>Number of allowances for Regular Withholding Allowances, Worksheet A (Note: The value should be numeric value of no more than 2 digits)
          </wta:Question><wta:Response>1</wta:Response></wta:ScriptQuestion><wta:ScriptQuestion><wta:Question>Number of allowances from the estimated Deductions, Worksheet B (Note: The value should be numeric value of no more than 2 digits)
          </wta:Question><wta:Response>1</wta:Response></wta:ScriptQuestion><wta:ScriptQuestion><wta:Question>Total Number of allowances (A+B) when using the California Withholding Schedules for the current year. (Note: The value should be numeric value of no more than 2 digits)
          </wta:Question><wta:Response>1</wta:Response></wta:ScriptQuestion><wta:ScriptQuestion><wta:Question>Additional amount of State income tax to be withheld each pay period(if employer agrees), Worksheet C (Note: Please enter an additional dollar amount that you wish to have withheld from your paycheck or enter zero)
          </wta:Question><wta:Response>1</wta:Response></wta:ScriptQuestion><wta:ScriptQuestion><wta:Question>Under the penalties of perjury, I certify that the number of withholding allowances claimed on this certificate does not exceed the number to which I am entitled or, if claiming exemption from withholding, that I am entitled to claim the exempt status.
          </wta:Question><wta:Response>Check here to agree</wta:Response></wta:ScriptQuestion></wta:Script></wta:Scripts></wta:StateTaxComplete><wta:PositionKey><wta:Name>Sales Associate 15</wta:Name><wta:Key /></wta:PositionKey><wta:CustomerSpecificValues><wta:CustomerSpecificValue><wta:Category>CustomerSpecific</wta:Category><wta:Name>CLUB_BEV_NUMBER</wta:Name><wta:Value>999</wta:Value></wta:CustomerSpecificValue><wta:CustomerSpecificValue><wta:Category>CustomerSpecific</wta:Category><wta:Name>REPORTS_TO_FIRSTNAME_LASTNAME</wta:Name><wta:Value>Casey pozzi</wta:Value></wta:CustomerSpecificValue></wta:CustomerSpecificValues></wta:HireData></wta:ADPUserArea></UserArea></NewHire></NewHires> 

It seems that the XML document does survive the ordeal when you copy into the posting window. The posting UI has a button which is difficult to discover that permits you insert code without it being molested. This is the button with two arrows pointing from each other. Although, for a document of this size, it would be better to upload it somewhere (Dropbox, SkyDrive etc.)

I would recommend using XPath and XQuery rather than OPENXML, but I cannot show any examples as long as I can't get the XML accepted.

I apologize that you were unable to extract the sample XML file.  I am new to XML development and seeking for help from anyone.  I am open for any suggestion like XPath or XQuery.  You are right, I had been reading OpenXML document and don't think it would be able to fulfill my needs.  I need to extract information from multiple child nodes.  However, I am not familiar how to use XQuery or XPath to extract the elements from different child nodes.  If someone can shred some light to extract the elements from the child nodes, I really appreciated.  

I am attaching the XML file from the <> icon and hope you can refer to.

Many thanks!

Brian.

<?xml version="1.0" encoding="utf-8"?><NewHires batchId="8583866" batchDate="4/17/2015 12:30:04 PM" recordCount="1"><NewHire xmlns="http://ns.hr-xml.org/2007-04-15" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:wta="http://www.ADP.com/wta"><TypeOfHire><StandardValue>NewHire</StandardValue></TypeOfHire><EmployeeInfo><PersonName><GivenName>applicant</GivenName><MiddleName /><FamilyName>fourteen</FamilyName></PersonName><ApplicantId idOwner="ADP"><IdValue name="candidateId">194018914</IdValue></ApplicantId><ContactMethod><Telephone><FormattedNumber>555-555-5555</FormattedNumber></Telephone><InternetEmailAddress>pozzic@XYZ.com</InternetEmailAddress><PostalAddress type="streetAddress"><CountryCode>US</CountryCode><PostalCode>94520</PostalCode><Region>CA</Region><Municipality>Concord</Municipality><DeliveryAddress><AddressLine>100 Broadway Street Road, Suite 900</AddressLine></DeliveryAddress></PostalAddress></ContactMethod><EmergencyContact><PersonName><FormattedName>fdgh dfgd</FormattedName><GivenName>fdgh</GivenName><FamilyName>dfgd</FamilyName></PersonName><ContactMethod><Telephone><FormattedNumber>555-555-5555</FormattedNumber></Telephone><PostalAddress type="streetAddress"><CountryCode>US</CountryCode><PostalCode>55555</PostalCode><Region>ca</Region><Municipality>fgh</Municipality><DeliveryAddress><AddressLine>fdg</AddressLine></DeliveryAddress></PostalAddress></ContactMethod></EmergencyContact><PersonDescriptors><LegalIdentifiers><PersonLegalId validFrom="notKnown" validTo="notKnown" idOwner="SSA" countryCode="US" jurisdiction="Country" issuingRegion="US" documentType="Social Security Card"><IdValue name="SSN">014000000</IdValue></PersonLegalId></LegalIdentifiers><DemographicDescriptors><Race /><Ethnicity>Yes</Ethnicity></DemographicDescriptors><BiologicalDescriptors><DateOfBirth>1972-04-20</DateOfBirth><GenderCode>2</GenderCode></BiologicalDescriptors></PersonDescriptors></EmployeeInfo><ApplicationInfo><ApplicationHistory><HiringProcessActivity><Type>HireStatusChange</Type><ActivityPerformer><PersonName><GivenName>Casey</GivenName><FamilyName>Pozzi</FamilyName></PersonName><PersonId idOwner="ADP"><IdValue>POZZIC</IdValue></PersonId></ActivityPerformer><Date>2015-04-16</Date></HiringProcessActivity></ApplicationHistory><WorkEligibilityInfo><DocumentId><IdValue>abcdefg</IdValue></DocumentId><DocumentName>U.S. Passport</DocumentName><IssuingAuthority>abcdefg</IssuingAuthority></WorkEligibilityInfo></ApplicationInfo><PositionInfo><ReferenceInfo><PositionId><IdValue name="positionId">110</IdValue></PositionId></ReferenceInfo><OfferInfo><NegotiatedPositionTitle>Sales Associate</NegotiatedPositionTitle><NegotiatedPositionDescription /><EmploymentStartDate>2015-04-16</EmploymentStartDate><RemunerationInfo><BasePay currencyCode="USD" baseInterval="Hourly">10.00</BasePay></RemunerationInfo><ResourceRelationship>Employee</ResourceRelationship></OfferInfo></PositionInfo><UserArea><wta:ADPUserArea><wta:ApplicationData><wta:ApplicationDate>2015-03-20</wta:ApplicationDate><wta:PositionApplied><wta:JobCode>110</wta:JobCode><wta:Title>Sales Associate</wta:Title></wta:PositionApplied><wta:AvailableToStartDate>2015-03-19</wta:AvailableToStartDate><wta:PostalAddresses><wta:PostalAddress type="current"><wta:CountryCode>US</wta:CountryCode><wta:PostalCode>94520</wta:PostalCode><wta:Region>CA</wta:Region><wta:Municipality>Concord</wta:Municipality><wta:DeliveryAddress><wta:AddressLine>100 Broadway Street Road, Suite 900</wta:AddressLine></wta:DeliveryAddress></wta:PostalAddress></wta:PostalAddresses><wta:Application><wta:Name>XYZ Non-Req External Seeker Site</wta:Name><wta:Locator>XYZNonReqExt</wta:Locator></wta:Application><wta:Referral><wta:Source>IJB</wta:Source><wta:SpecificSource>CommunityWebsite</wta:SpecificSource></wta:Referral><wta:Authorization><wta:Disclosures><wta:Disclosure type="FCRA" question="Title"><wta:Question>FAIR CREDIT REPORTING ACT (FCRA) DISCLOSURE AND AUTHORIZATION</wta:Question></wta:Disclosure><wta:Disclosure type="FCRA" question="Authorization"><wta:Question>For employment purposes XYZ! will obtain a report (called a &amp;quot;Consumer Report&amp;quot;) from SterlingBackcheck, 1 State Street Plaza, 24th Floor, New York, NY 10004, 1-800-899-2272, http://www.sterlingbackcheck.com/ (called a &amp;quot;Consumer Reporting Agency&amp;quot;). This report will be used in whole or in part to determine your qualifications and suitability for employment and, to the extent permitted by law, it may include information about your character, general reputation, personal characteristics, mode of living, and credit history. The report could also include motor vehicle or driving record, criminal and civil records, prior employment, education and other public record information. Your authorization will apply pre-employment and, if you are hired, will also apply throughout your employment unless prohibited by law.</wta:Question><wta:Answer>I authorize the procurement of a Consumer Report</wta:Answer></wta:Disclosure><wta:Disclosure type="FCRA" question="SendCopyToApplicant"><wta:Question>For California applicants only: A consumer credit report may be used and, if so, it will be obtained from SterlingBackcheck. An investigative consumer report, as defined under California law, will be obtained for employment purposes, and it may include information about your character, general reputation, personal characteristics, and mode of living. XYZ! may/will request an investigation regarding your credit history, motor vehicle or driving record(s), criminal and civil records, prior employment (including contacting prior employers), education as well as other public record information or information related to your background from SterlingBackcheck. The results of this report may be used as a factor in making employment decisions. Under section 1786.22 of the California Civil Code, you may view the file maintained on you by the consumer reporting agency named above during normal business hours upon reasonable notice. You may also obtain a copy of this file upon submitting proper identification and paying the costs of duplication services, by appearing at the Consumer Reporting Agency identified above in person or by mail. The Consumer Reporting Agency cannot charge you more than the actual copying costs for providing you with a copy of your file. You may also receive a summary of the file by telephone. To do so, you must first make a written request, with proper identification, seeking telephone disclosure and pay for any toll-charges for the call prior to such a call. The agency is required to have personnel available to explain your file to you and the agency must explain to you any coded information appearing in your file. If you appear in person, a person of your choice may accompany you, provided that this person furnishes proper identification. You can also request that a copy of your report be sent to a specific addressee by certified mail. The Consumer Reporting Agency will not be liable for disclosures of your report to any third parties caused by mishandling of such mail after the CRA sends it. &amp;quot;Proper identification&amp;quot; includes documents such as a valid driver&amp;#39;s license, social security account number, military identification card, and credit cards. Only if you cannot identify yourself with such information may the CRA require additional information concerning your employment or personal history in order to verify your identity. You have a right to obtain a free copy of any report obtained by XYZ!. Do you require a mailed copy of the report? For Washington applicants only: A consumer report may be obtained in connection with this application.</wta:Question><wta:Answer>No</wta:Answer></wta:Disclosure></wta:Disclosures><wta:SendCopyToApplicant>No</wta:SendCopyToApplicant></wta:Authorization></wta:ApplicationData><wta:HireData><wta:HireDate>2015-04-16</wta:HireDate><wta:Requestor><wta:PersonName><wta:GivenName>Casey</wta:GivenName><wta:FamilyName>Pozzi</wta:FamilyName></wta:PersonName><wta:Username>POZZIC</wta:Username></wta:Requestor><wta:Location type="store"><wta:StoreNumber>88</wta:StoreNumber><wta:Name>Burbank</wta:Name><wta:PostalAddress><wta:CountryCode>US</wta:CountryCode><wta:PostalCode>91502</wta:PostalCode><wta:Region>CA</wta:Region><wta:Municipality>Burbank</wta:Municipality></wta:PostalAddress><wta:Parent><wta:Location type="district"><wta:StoreNumber>10009</wta:StoreNumber><wta:Name>Richcreek</wta:Name><wta:PostalAddress><wta:CountryCode>US</wta:CountryCode></wta:PostalAddress><wta:Parent><wta:Location type="region"><wta:StoreNumber>902</wta:StoreNumber><wta:Name>Southern California</wta:Name><wta:PostalAddress><wta:CountryCode>US</wta:CountryCode></wta:PostalAddress><wta:Parent><wta:Location type="corporate"><wta:StoreNumber>900</wta:StoreNumber><wta:Name>XYZ!</wta:Name><wta:PostalAddress><wta:CountryCode>US</wta:CountryCode><wta:PostalCode>94520</wta:PostalCode><wta:Region>CA</wta:Region><wta:Municipality>Concord</wta:Municipality></wta:PostalAddress></wta:Location></wta:Parent></wta:Location></wta:Parent></wta:Location></wta:Parent></wta:Location><wta:W4TaxWithholdingInfo><wta:AllowanceA /><wta:AllowanceB /><wta:AllowanceC /><wta:AllowanceD /><wta:AllowanceE /><wta:AllowanceF /><wta:AllowanceG /><wta:AllowanceH /><wta:TotalAllowance>1</wta:TotalAllowance><wta:AdditionalWithholding /><wta:FilingStatus>Married</wta:FilingStatus><wta:IsW4Exempt>No</wta:IsW4Exempt><wta:IsW4LastNameDifferent>false</wta:IsW4LastNameDifferent><wta:ExemptText /></wta:W4TaxWithholdingInfo><wta:I9TaxWithholdingInfo><wta:AlienOrAdmissionNumber /><wta:AlienOrAdmissionType /><wta:MaidenName>dfgdf</wta:MaidenName><wta:BeganEmploymentDate>2015-04-16</wta:BeganEmploymentDate><wta:PreparerName /><wta:PreparerAddress /><wta:EligibilityToWork>USCitizen</wta:EligibilityToWork><wta:ForeignPassportNumber /><wta:IssuanceCountry /><wta:EmailAddress /><wta:PhoneNumber /><wta:IdentityEligibilityListSelection>ListA</wta:IdentityEligibilityListSelection><wta:IdentityEligibilityListA><wta:DocumentType>USPassport</wta:DocumentType><wta:IssuingAuthority>abcdefg</wta:IssuingAuthority><wta:DocumentNumber>abcdefg</wta:DocumentNumber><wta:ReceiptDocument /><wta:SecondDocumentNumber /><wta:ReceiptSecondDocument /><wta:IssuingAuthoritySecond /><wta:ThirdDocumentNumber /><wta:ReceiptThirdDocument /><wta:IssuingAuthorityThird /></wta:IdentityEligibilityListA></wta:I9TaxWithholdingInfo><wta:StateTaxInfo><wta:StateAdditionalTax>1</wta:StateAdditionalTax><wta:StateMaritalStatus>M</wta:StateMaritalStatus><wta:StateAllowances>1</wta:StateAllowances><wta:StateExempt>N</wta:StateExempt></wta:StateTaxInfo><wta:StateTaxComplete><wta:Scripts><wta:Script><wta:ScriptName>State Tax Script California</wta:ScriptName><wta:ScriptQuestion><wta:Question>Filling Status Withholding Allowances.
          </wta:Question><wta:Response>MARRIED(with one income)</wta:Response></wta:ScriptQuestion><wta:ScriptQuestion><wta:Question>Are you subjected to California withholdings?
          </wta:Question><wta:Response>Yes</wta:Response></wta:ScriptQuestion><wta:ScriptQuestion><wta:Question>Please Note: To view this form, close Onboarding. Sign in and then go to the State form page. Click the link to view the form. Keep it open as you answer the questions.
          </wta:Question><wta:Response /></wta:ScriptQuestion><wta:ScriptQuestion><wta:Question>Number of allowances for Regular Withholding Allowances, Worksheet A (Note: The value should be numeric value of no more than 2 digits)
          </wta:Question><wta:Response>1</wta:Response></wta:ScriptQuestion><wta:ScriptQuestion><wta:Question>Number of allowances from the estimated Deductions, Worksheet B (Note: The value should be numeric value of no more than 2 digits)
          </wta:Question><wta:Response>1</wta:Response></wta:ScriptQuestion><wta:ScriptQuestion><wta:Question>Total Number of allowances (A+B) when using the California Withholding Schedules for the current year. (Note: The value should be numeric value of no more than 2 digits)
          </wta:Question><wta:Response>1</wta:Response></wta:ScriptQuestion><wta:ScriptQuestion><wta:Question>Additional amount of State income tax to be withheld each pay period(if employer agrees), Worksheet C (Note: Please enter an additional dollar amount that you wish to have withheld from your paycheck or enter zero)
          </wta:Question><wta:Response>1</wta:Response></wta:ScriptQuestion><wta:ScriptQuestion><wta:Question>Under the penalties of perjury, I certify that the number of withholding allowances claimed on this certificate does not exceed the number to which I am entitled or, if claiming exemption from withholding, that I am entitled to claim the exempt status.
          </wta:Question><wta:Response>Check here to agree</wta:Response></wta:ScriptQuestion></wta:Script></wta:Scripts></wta:StateTaxComplete><wta:PositionKey><wta:Name>Sales Associate 15</wta:Name><wta:Key /></wta:PositionKey><wta:CustomerSpecificValues><wta:CustomerSpecificValue><wta:Category>CustomerSpecific</wta:Category><wta:Name>CLUB_BEV_NUMBER</wta:Name><wta:Value>999</wta:Value></wta:CustomerSpecificValue><wta:CustomerSpecificValue><wta:Category>CustomerSpecific</wta:Category><wta:Name>REPORTS_TO_FIRSTNAME_LASTNAME</wta:Name><wta:Value>Casey pozzi</wta:Value></wta:CustomerSpecificValue></wta:CustomerSpecificValues></wta:HireData></wta:ADPUserArea></UserArea></NewHire></NewHires>

Hi Eric,

Here is the XML file for your refer in case you need it.

Thanks!

Brian.

<?xml version="1.0" encoding="utf-8"?><NewHires batchId="8583866" batchDate="4/17/2015 12:30:04 PM" recordCount="1"><NewHire xmlns="http://ns.hr-xml.org/2007-04-15" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:wta="http://www.ADP.com/wta"><TypeOfHire><StandardValue>NewHire</StandardValue></TypeOfHire><EmployeeInfo><PersonName><GivenName>applicant</GivenName><MiddleName /><FamilyName>fourteen</FamilyName></PersonName><ApplicantId idOwner="ADP"><IdValue name="candidateId">194018914</IdValue></ApplicantId><ContactMethod><Telephone><FormattedNumber>555-555-5555</FormattedNumber></Telephone><InternetEmailAddress>pozzic@XYZ.com</InternetEmailAddress><PostalAddress type="streetAddress"><CountryCode>US</CountryCode><PostalCode>94520</PostalCode><Region>CA</Region><Municipality>Concord</Municipality><DeliveryAddress><AddressLine>100 Broadway Street Road, Suite 900</AddressLine></DeliveryAddress></PostalAddress></ContactMethod><EmergencyContact><PersonName><FormattedName>fdgh dfgd</FormattedName><GivenName>fdgh</GivenName><FamilyName>dfgd</FamilyName></PersonName><ContactMethod><Telephone><FormattedNumber>555-555-5555</FormattedNumber></Telephone><PostalAddress type="streetAddress"><CountryCode>US</CountryCode><PostalCode>55555</PostalCode><Region>ca</Region><Municipality>fgh</Municipality><DeliveryAddress><AddressLine>fdg</AddressLine></DeliveryAddress></PostalAddress></ContactMethod></EmergencyContact><PersonDescriptors><LegalIdentifiers><PersonLegalId validFrom="notKnown" validTo="notKnown" idOwner="SSA" countryCode="US" jurisdiction="Country" issuingRegion="US" documentType="Social Security Card"><IdValue name="SSN">014000000</IdValue></PersonLegalId></LegalIdentifiers><DemographicDescriptors><Race /><Ethnicity>Yes</Ethnicity></DemographicDescriptors><BiologicalDescriptors><DateOfBirth>1972-04-20</DateOfBirth><GenderCode>2</GenderCode></BiologicalDescriptors></PersonDescriptors></EmployeeInfo><ApplicationInfo><ApplicationHistory><HiringProcessActivity><Type>HireStatusChange</Type><ActivityPerformer><PersonName><GivenName>Casey</GivenName><FamilyName>Pozzi</FamilyName></PersonName><PersonId idOwner="ADP"><IdValue>POZZIC</IdValue></PersonId></ActivityPerformer><Date>2015-04-16</Date></HiringProcessActivity></ApplicationHistory><WorkEligibilityInfo><DocumentId><IdValue>abcdefg</IdValue></DocumentId><DocumentName>U.S. Passport</DocumentName><IssuingAuthority>abcdefg</IssuingAuthority></WorkEligibilityInfo></ApplicationInfo><PositionInfo><ReferenceInfo><PositionId><IdValue name="positionId">110</IdValue></PositionId></ReferenceInfo><OfferInfo><NegotiatedPositionTitle>Sales Associate</NegotiatedPositionTitle><NegotiatedPositionDescription /><EmploymentStartDate>2015-04-16</EmploymentStartDate><RemunerationInfo><BasePay currencyCode="USD" baseInterval="Hourly">10.00</BasePay></RemunerationInfo><ResourceRelationship>Employee</ResourceRelationship></OfferInfo></PositionInfo><UserArea><wta:ADPUserArea><wta:ApplicationData><wta:ApplicationDate>2015-03-20</wta:ApplicationDate><wta:PositionApplied><wta:JobCode>110</wta:JobCode><wta:Title>Sales Associate</wta:Title></wta:PositionApplied><wta:AvailableToStartDate>2015-03-19</wta:AvailableToStartDate><wta:PostalAddresses><wta:PostalAddress type="current"><wta:CountryCode>US</wta:CountryCode><wta:PostalCode>94520</wta:PostalCode><wta:Region>CA</wta:Region><wta:Municipality>Concord</wta:Municipality><wta:DeliveryAddress><wta:AddressLine>100 Broadway Street Road, Suite 900</wta:AddressLine></wta:DeliveryAddress></wta:PostalAddress></wta:PostalAddresses><wta:Application><wta:Name>XYZ Non-Req External Seeker Site</wta:Name><wta:Locator>XYZNonReqExt</wta:Locator></wta:Application><wta:Referral><wta:Source>IJB</wta:Source><wta:SpecificSource>CommunityWebsite</wta:SpecificSource></wta:Referral><wta:Authorization><wta:Disclosures><wta:Disclosure type="FCRA" question="Title"><wta:Question>FAIR CREDIT REPORTING ACT (FCRA) DISCLOSURE AND AUTHORIZATION</wta:Question></wta:Disclosure><wta:Disclosure type="FCRA" question="Authorization"><wta:Question>For employment purposes XYZ! will obtain a report (called a &amp;quot;Consumer Report&amp;quot;) from SterlingBackcheck, 1 State Street Plaza, 24th Floor, New York, NY 10004, 1-800-899-2272, http://www.sterlingbackcheck.com/ (called a &amp;quot;Consumer Reporting Agency&amp;quot;). This report will be used in whole or in part to determine your qualifications and suitability for employment and, to the extent permitted by law, it may include information about your character, general reputation, personal characteristics, mode of living, and credit history. The report could also include motor vehicle or driving record, criminal and civil records, prior employment, education and other public record information. Your authorization will apply pre-employment and, if you are hired, will also apply throughout your employment unless prohibited by law.</wta:Question><wta:Answer>I authorize the procurement of a Consumer Report</wta:Answer></wta:Disclosure><wta:Disclosure type="FCRA" question="SendCopyToApplicant"><wta:Question>For California applicants only: A consumer credit report may be used and, if so, it will be obtained from SterlingBackcheck. An investigative consumer report, as defined under California law, will be obtained for employment purposes, and it may include information about your character, general reputation, personal characteristics, and mode of living. XYZ! may/will request an investigation regarding your credit history, motor vehicle or driving record(s), criminal and civil records, prior employment (including contacting prior employers), education as well as other public record information or information related to your background from SterlingBackcheck. The results of this report may be used as a factor in making employment decisions. Under section 1786.22 of the California Civil Code, you may view the file maintained on you by the consumer reporting agency named above during normal business hours upon reasonable notice. You may also obtain a copy of this file upon submitting proper identification and paying the costs of duplication services, by appearing at the Consumer Reporting Agency identified above in person or by mail. The Consumer Reporting Agency cannot charge you more than the actual copying costs for providing you with a copy of your file. You may also receive a summary of the file by telephone. To do so, you must first make a written request, with proper identification, seeking telephone disclosure and pay for any toll-charges for the call prior to such a call. The agency is required to have personnel available to explain your file to you and the agency must explain to you any coded information appearing in your file. If you appear in person, a person of your choice may accompany you, provided that this person furnishes proper identification. You can also request that a copy of your report be sent to a specific addressee by certified mail. The Consumer Reporting Agency will not be liable for disclosures of your report to any third parties caused by mishandling of such mail after the CRA sends it. &amp;quot;Proper identification&amp;quot; includes documents such as a valid driver&amp;#39;s license, social security account number, military identification card, and credit cards. Only if you cannot identify yourself with such information may the CRA require additional information concerning your employment or personal history in order to verify your identity. You have a right to obtain a free copy of any report obtained by XYZ!. Do you require a mailed copy of the report? For Washington applicants only: A consumer report may be obtained in connection with this application.</wta:Question><wta:Answer>No</wta:Answer></wta:Disclosure></wta:Disclosures><wta:SendCopyToApplicant>No</wta:SendCopyToApplicant></wta:Authorization></wta:ApplicationData><wta:HireData><wta:HireDate>2015-04-16</wta:HireDate><wta:Requestor><wta:PersonName><wta:GivenName>Casey</wta:GivenName><wta:FamilyName>Pozzi</wta:FamilyName></wta:PersonName><wta:Username>POZZIC</wta:Username></wta:Requestor><wta:Location type="store"><wta:StoreNumber>88</wta:StoreNumber><wta:Name>Burbank</wta:Name><wta:PostalAddress><wta:CountryCode>US</wta:CountryCode><wta:PostalCode>91502</wta:PostalCode><wta:Region>CA</wta:Region><wta:Municipality>Burbank</wta:Municipality></wta:PostalAddress><wta:Parent><wta:Location type="district"><wta:StoreNumber>10009</wta:StoreNumber><wta:Name>Richcreek</wta:Name><wta:PostalAddress><wta:CountryCode>US</wta:CountryCode></wta:PostalAddress><wta:Parent><wta:Location type="region"><wta:StoreNumber>902</wta:StoreNumber><wta:Name>Southern California</wta:Name><wta:PostalAddress><wta:CountryCode>US</wta:CountryCode></wta:PostalAddress><wta:Parent><wta:Location type="corporate"><wta:StoreNumber>900</wta:StoreNumber><wta:Name>XYZ!</wta:Name><wta:PostalAddress><wta:CountryCode>US</wta:CountryCode><wta:PostalCode>94520</wta:PostalCode><wta:Region>CA</wta:Region><wta:Municipality>Concord</wta:Municipality></wta:PostalAddress></wta:Location></wta:Parent></wta:Location></wta:Parent></wta:Location></wta:Parent></wta:Location><wta:W4TaxWithholdingInfo><wta:AllowanceA /><wta:AllowanceB /><wta:AllowanceC /><wta:AllowanceD /><wta:AllowanceE /><wta:AllowanceF /><wta:AllowanceG /><wta:AllowanceH /><wta:TotalAllowance>1</wta:TotalAllowance><wta:AdditionalWithholding /><wta:FilingStatus>Married</wta:FilingStatus><wta:IsW4Exempt>No</wta:IsW4Exempt><wta:IsW4LastNameDifferent>false</wta:IsW4LastNameDifferent><wta:ExemptText /></wta:W4TaxWithholdingInfo><wta:I9TaxWithholdingInfo><wta:AlienOrAdmissionNumber /><wta:AlienOrAdmissionType /><wta:MaidenName>dfgdf</wta:MaidenName><wta:BeganEmploymentDate>2015-04-16</wta:BeganEmploymentDate><wta:PreparerName /><wta:PreparerAddress /><wta:EligibilityToWork>USCitizen</wta:EligibilityToWork><wta:ForeignPassportNumber /><wta:IssuanceCountry /><wta:EmailAddress /><wta:PhoneNumber /><wta:IdentityEligibilityListSelection>ListA</wta:IdentityEligibilityListSelection><wta:IdentityEligibilityListA><wta:DocumentType>USPassport</wta:DocumentType><wta:IssuingAuthority>abcdefg</wta:IssuingAuthority><wta:DocumentNumber>abcdefg</wta:DocumentNumber><wta:ReceiptDocument /><wta:SecondDocumentNumber /><wta:ReceiptSecondDocument /><wta:IssuingAuthoritySecond /><wta:ThirdDocumentNumber /><wta:ReceiptThirdDocument /><wta:IssuingAuthorityThird /></wta:IdentityEligibilityListA></wta:I9TaxWithholdingInfo><wta:StateTaxInfo><wta:StateAdditionalTax>1</wta:StateAdditionalTax><wta:StateMaritalStatus>M</wta:StateMaritalStatus><wta:StateAllowances>1</wta:StateAllowances><wta:StateExempt>N</wta:StateExempt></wta:StateTaxInfo><wta:StateTaxComplete><wta:Scripts><wta:Script><wta:ScriptName>State Tax Script California</wta:ScriptName><wta:ScriptQuestion><wta:Question>Filling Status Withholding Allowances.
          </wta:Question><wta:Response>MARRIED(with one income)</wta:Response></wta:ScriptQuestion><wta:ScriptQuestion><wta:Question>Are you subjected to California withholdings?
          </wta:Question><wta:Response>Yes</wta:Response></wta:ScriptQuestion><wta:ScriptQuestion><wta:Question>Please Note: To view this form, close Onboarding. Sign in and then go to the State form page. Click the link to view the form. Keep it open as you answer the questions.
          </wta:Question><wta:Response /></wta:ScriptQuestion><wta:ScriptQuestion><wta:Question>Number of allowances for Regular Withholding Allowances, Worksheet A (Note: The value should be numeric value of no more than 2 digits)
          </wta:Question><wta:Response>1</wta:Response></wta:ScriptQuestion><wta:ScriptQuestion><wta:Question>Number of allowances from the estimated Deductions, Worksheet B (Note: The value should be numeric value of no more than 2 digits)
          </wta:Question><wta:Response>1</wta:Response></wta:ScriptQuestion><wta:ScriptQuestion><wta:Question>Total Number of allowances (A+B) when using the California Withholding Schedules for the current year. (Note: The value should be numeric value of no more than 2 digits)
          </wta:Question><wta:Response>1</wta:Response></wta:ScriptQuestion><wta:ScriptQuestion><wta:Question>Additional amount of State income tax to be withheld each pay period(if employer agrees), Worksheet C (Note: Please enter an additional dollar amount that you wish to have withheld from your paycheck or enter zero)
          </wta:Question><wta:Response>1</wta:Response></wta:ScriptQuestion><wta:ScriptQuestion><wta:Question>Under the penalties of perjury, I certify that the number of withholding allowances claimed on this certificate does not exceed the number to which I am entitled or, if claiming exemption from withholding, that I am entitled to claim the exempt status.
          </wta:Question><wta:Response>Check here to agree</wta:Response></wta:ScriptQuestion></wta:Script></wta:Scripts></wta:StateTaxComplete><wta:PositionKey><wta:Name>Sales Associate 15</wta:Name><wta:Key /></wta:PositionKey><wta:CustomerSpecificValues><wta:CustomerSpecificValue><wta:Category>CustomerSpecific</wta:Category><wta:Name>CLUB_BEV_NUMBER</wta:Name><wta:Value>999</wta:Value></wta:CustomerSpecificValue><wta:CustomerSpecificValue><wta:Category>CustomerSpecific</wta:Category><wta:Name>REPORTS_TO_FIRSTNAME_LASTNAME</wta:Name><wta:Value>Casey pozzi</wta:Value></wta:CustomerSpecificValue></wta:CustomerSpecificValues></wta:HireData></wta:ADPUserArea></UserArea></NewHire></NewHires>

Hi Eric,
If I use below root path command, I will get below result which list the elements like below.  If does not give me the column name and the result split into multiple rows.
 
SELECT *
FROM
OPENXML (@hdoc, '/', 2)

id parentid nodetype localname prefix namespaceuri datatype prev text
0 NULL 1 NewHires NULL NULL NULL NULL NULL
2 0 2 batchId NULL NULL NULL NULL NULL
2891 2 3 #text NULL NULL NULL NULL 8569021
3 0 2 batchDate NULL NULL NULL NULL NULL

That is what OPENXML supposed to do. As forementioned, if you need to get the value from a specific position, you need to specify the path in the WITH clause.

SELECT *
 FROM OPENXML (@hdoc, '/', 2)
WITH
(
  GivenName VARCHAR(100) '/NewHires/a:NewHire/a:EmployeeInfo/a:PersonName/a:GivenName',
  MiddleName VARCHAR(100) '/NewHires/a:NewHire/a:EmployeeInfo/a:PersonName/a:MiddleName',
  FamilyName VARCHAR(100) '/NewHires/a:NewHire/a:EmployeeInfo/a:PersonName/a:FamilyName'
)

I did post a link in your last thread for XQUERY, I don't mind posting it in this case again, please see this link. However for both OPENXML and XQUERY, you can't work around the path and namespace.

If you can be more specific on your requirement, namely what is expected to extract from that XML, we can give further advice.

For any question, feel free to let me know

The XML document now parses correctly. Unfortunately, those namespace declaration makes it a lot more difficult. I played around with an example, but I was not able to get it to work.

Hi Erland,

Thank you for trying.  Eric was able to give me a hint and I was able to retrieve elements from different child nodes.  There are three namespaces in this XML file which creates the complexity. 

Many thanks to all of you!

Brian.