Inserting data using OPENXML

Question

Post reply

Inserting data using OPENXML

thesqlcoder

Mr or Mrs. 500

Points: 572
More actions
June 21, 2017 at 3:31 am

#151597

Hi, I received am xml file from the customer (Around 2 gb data). The xml looks complex. I have to store data into sql table. Can you please help how to fetch data from this xml.
Also please provide if there is any other alternative. Can i use sqlxml to insert data and in that case it will require xsd so is ther any way to create single xsd from below xml?
XML Sample:
<Reports>
<Report IDNumberType="RegistrationNumber" IDNumber="40001000084">
   <CustomReport xmlns="http://miscellaneous.com/CB5/v5.33/CustomReport" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
    <Agreements i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Agreements" />
    <CIP i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/CIP" />
    <CIQ i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/CIQ" />
    <CIR i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/CIR" />
    <Company i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Company" />
    <CompanySimple i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/CompanySimple" />
    <ContractOverview i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/ContractOverview" />
    <ContractSummary i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/ContractSummary" />
    <Contracts i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Contracts" />
    <CurrentRelations i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/CurrentRelations" />
    <Dashboard i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Dashboard" />
    <DelinquencyStatus i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/DelinquencyStatus" />
    <Disputes i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Disputes" />
    <FinancialStatements i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/FinancialStatements" />
    <Individual i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Individual" />
    <IndividualSimple i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/IndividualSimple" />
    <Inquiries i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Inquiries" />
    <Insolvencies xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Insolvencies">
      <a:InsolvencyList />
    </Insolvencies>
    <Liquidations i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Liquidations" />
    <NegativeContractList i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/NegativeContractList" />
    <NegativePaymentIncidentList i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/NegativePaymentIncidentList" />
    <Parameters>
      <Consent>true</Consent>
      <IDNumber>40001000084</IDNumber>
      <IDNumberType>RegistrationNumber</IDNumberType>
      <InquiryReason>CreditRiskManagement</InquiryReason>
      <ReportDate>2017-06-06T00:00:00</ReportDate>
      <Sections xmlns:a="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
       <a:string>Insolvencies</a:string>
      </Sections>
      <SubjectType>Company</SubjectType>
    </Parameters>
    <PaymentIncidentList i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/PaymentIncidentList" />
    <Pledges i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Pledges" />
    <ReportInfo xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/ReportInfo">
      <a:Created>2017-06-07T19:19:23.7948461+03:00</a:Created>
      <a:ReferenceNumber>10607036-2484240</a:ReferenceNumber>
      <a:ReportStatus>ReportGenerated</a:ReportStatus>
      <a:RequestedBy i:nil="true" />
      <a:Subscriber i:nil="true" />
      <a:Version>533</a:Version>
    </ReportInfo>
    <Restrictions i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Restrictions" />
    <SubjectInfoHistory i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/SubjectInfoHistory" />
   </CustomReport>
</Report>
<Report IDNumberType="RegistrationNumber" IDNumber="40001000099">
   <CustomReport xmlns="http://miscellaneous.com/CB5/v5.33/CustomReport" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
    <Agreements i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Agreements" />
    <CIP i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/CIP" />
    <CIQ i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/CIQ" />
    <CIR i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/CIR" />
    <Company i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Company" />
    <CompanySimple i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/CompanySimple" />
    <ContractOverview i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/ContractOverview" />
    <ContractSummary i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/ContractSummary" />
    <Contracts i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Contracts" />
    <CurrentRelations i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/CurrentRelations" />
    <Dashboard i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Dashboard" />
    <DelinquencyStatus i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/DelinquencyStatus" />
    <Disputes i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Disputes" />
    <FinancialStatements i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/FinancialStatements" />
    <Individual i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Individual" />
    <IndividualSimple i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/IndividualSimple" />
    <Inquiries i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Inquiries" />
    <Insolvencies xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Insolvencies">
      <a:InsolvencyList />
    </Insolvencies>
    <Liquidations i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Liquidations" />
    <NegativeContractList i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/NegativeContractList" />
    <NegativePaymentIncidentList i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/NegativePaymentIncidentList" />
    <Parameters>
      <Consent>true</Consent>
      <IDNumber>40001000099</IDNumber>
      <IDNumberType>RegistrationNumber</IDNumberType>
      <InquiryReason>CreditRiskManagement</InquiryReason>
      <ReportDate>2017-06-06T00:00:00</ReportDate>
      <Sections xmlns:a="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
       <a:string>Insolvencies</a:string>
      </Sections>
      <SubjectType>Company</SubjectType>
    </Parameters>
    <PaymentIncidentList i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/PaymentIncidentList" />
    <Pledges i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Pledges" />
    <ReportInfo xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/ReportInfo">
      <a:Created>2017-06-07T19:19:23.8729864+03:00</a:Created>
      <a:ReferenceNumber>10607037-8414032</a:ReferenceNumber>
      <a:ReportStatus>ReportGenerated</a:ReportStatus>
      <a:RequestedBy i:nil="true" />
      <a:Subscriber i:nil="true" />
      <a:Version>533</a:Version>
    </ReportInfo>
    <Restrictions i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Restrictions" />
    <SubjectInfoHistory i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/SubjectInfoHistory" />
   </CustomReport>
</Report>
<Report IDNumberType="RegistrationNumber" IDNumber="40001000262">
   <CustomReport xmlns="http://miscellaneous.com/CB5/v5.33/CustomReport" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
    <Agreements i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Agreements" />
    <CIP i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/CIP" />
    <CIQ i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/CIQ" />
    <CIR i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/CIR" />
    <Company i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Company" />
    <CompanySimple i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/CompanySimple" />
    <ContractOverview i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/ContractOverview" />
    <ContractSummary i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/ContractSummary" />
    <Contracts i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Contracts" />
    <CurrentRelations i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/CurrentRelations" />
    <Dashboard i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Dashboard" />
    <DelinquencyStatus i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/DelinquencyStatus" />
    <Disputes i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Disputes" />
    <FinancialStatements i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/FinancialStatements" />
    <Individual i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Individual" />
    <IndividualSimple i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/IndividualSimple" />
    <Inquiries i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Inquiries" />
    <Insolvencies xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Insolvencies">
      <a:InsolvencyList />
    </Insolvencies>
    <Liquidations i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Liquidations" />
    <NegativeContractList i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/NegativeContractList" />
    <NegativePaymentIncidentList i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/NegativePaymentIncidentList" />
    <Parameters>
      <Consent>true</Consent>
      <IDNumber>40001000262</IDNumber>
      <IDNumberType>RegistrationNumber</IDNumberType>
      <InquiryReason>CreditRiskManagement</InquiryReason>
      <ReportDate>2017-06-06T00:00:00</ReportDate>
      <Sections xmlns:a="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
       <a:string>Insolvencies</a:string>
      </Sections>
      <SubjectType>Company</SubjectType>
    </Parameters>
    <PaymentIncidentList i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/PaymentIncidentList" />
    <Pledges i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Pledges" />
    <ReportInfo xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/ReportInfo">
      <a:Created>2017-06-07T19:19:23.9354796+03:00</a:Created>
      <a:ReferenceNumber>10607038-6427564</a:ReferenceNumber>
      <a:ReportStatus>ReportGenerated</a:ReportStatus>
      <a:RequestedBy i:nil="true" />
      <a:Subscriber i:nil="true" />
      <a:Version>533</a:Version>
    </ReportInfo>
    <Restrictions i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Restrictions" />
    <SubjectInfoHistory i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/SubjectInfoHistory" />
   </CustomReport>
</Report>
<Report IDNumberType="RegistrationNumber" IDNumber="40001001342">
   <CustomReport xmlns="http://miscellaneous.com/CB5/v5.33/CustomReport" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
    <Agreements i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Agreements" />
    <CIP i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/CIP" />
    <CIQ i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/CIQ" />
    <CIR i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/CIR" />
    <Company i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Company" />
    <CompanySimple i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/CompanySimple" />
    <ContractOverview i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/ContractOverview" />
    <ContractSummary i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/ContractSummary" />
    <Contracts i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Contracts" />
    <CurrentRelations i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/CurrentRelations" />
    <Dashboard i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Dashboard" />
    <DelinquencyStatus i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/DelinquencyStatus" />
    <Disputes i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Disputes" />
    <FinancialStatements i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/FinancialStatements" />
    <Individual i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Individual" />
    <IndividualSimple i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/IndividualSimple" />
    <Inquiries i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Inquiries" />
    <Insolvencies xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Insolvencies">
      <a:InsolvencyList>
       <a:Insolvency>
        <a:AccordingToLaw>Local</a:AccordingToLaw>
        <a:CreditorTermDays i:nil="true" />
        <a:CreditorTermMonths>1</a:CreditorTermMonths>
        <a:CreditorTermWeeks i:nil="true" />
        <a:CreditorTermYears i:nil="true" />
        <a:EventList>
          <a:Event>
           <a:AuctionPropertyDescription>NotSpecified</a:AuctionPropertyDescription>
           <a:CourtName>Rīgas rajona tiesa</a:CourtName>
           <a:DateCompletion i:nil="true" />
           <a:DateRecordRegistered>2016-07-28T00:00:00</a:DateRecordRegistered>
           <a:DateRecordTerminated i:nil="true" />
           <a:Description i:nil="true" />
           <a:EventDate>2016-07-27T00:00:00</a:EventDate>
           <a:EventForm>MNP</a:EventForm>
           <a:EventMethod>
            <EventMethod>NotSpecified</EventMethod>
           </a:EventMethod>
           <a:EventNumber>C33623316</a:EventNumber>
           <a:EventPlace i:nil="true" />
           <a:EventRegisteredDate>2016-07-28T00:00:00</a:EventRegisteredDate>
           <a:JudicialReasoning i:nil="true" />
           <a:RecordTerminationDecision i:nil="true" />
           <a:Term i:nil="true" />
           <a:TerminationForm>MNP</a:TerminationForm>
           <a:TerminationTermDays i:nil="true" />
           <a:TerminationTermMonths>1</a:TerminationTermMonths>
           <a:TerminationTermWeeks i:nil="true" />
           <a:TerminationTermYears i:nil="true" />
          </a:Event>
          <a:Event>
           <a:AuctionPropertyDescription>NotSpecified</a:AuctionPropertyDescription>
           <a:CourtName>Rīgas rajona tiesa</a:CourtName>
           <a:DateCompletion i:nil="true" />
           <a:DateRecordRegistered>2016-07-28T00:00:00</a:DateRecordRegistered>
           <a:DateRecordTerminated i:nil="true" />
           <a:Description i:nil="true" />
           <a:EventDate>2016-07-27T00:00:00</a:EventDate>
           <a:EventForm>ASM</a:EventForm>
           <a:EventMethod>
            <EventMethod>NotSpecified</EventMethod>
           </a:EventMethod>
           <a:EventNumber>C33623316</a:EventNumber>
           <a:EventPlace i:nil="true" />
           <a:EventRegisteredDate>2016-07-28T00:00:00</a:EventRegisteredDate>
           <a:JudicialReasoning i:nil="true" />
           <a:RecordTerminationDecision i:nil="true" />
           <a:Term i:nil="true" />
           <a:TerminationForm>NotSpecified</a:TerminationForm>
           <a:TerminationTermDays i:nil="true" />
           <a:TerminationTermMonths i:nil="true" />
           <a:TerminationTermWeeks i:nil="true" />
           <a:TerminationTermYears i:nil="true" />
          </a:Event>
          <a:Event>
           <a:AuctionPropertyDescription>NotSpecified</a:AuctionPropertyDescription>
           <a:CourtName>Rīgas rajona tiesa</a:CourtName>
           <a:DateCompletion i:nil="true" />
           <a:DateRecordRegistered>2016-11-09T00:00:00</a:DateRecordRegistered>
           <a:DateRecordTerminated i:nil="true" />
           <a:Description i:nil="true" />
           <a:EventDate>2016-11-07T00:00:00</a:EventDate>
           <a:EventForm>MNB</a:EventForm>
           <a:EventMethod>
            <EventMethod>NotSpecified</EventMethod>
           </a:EventMethod>
           <a:EventNumber>C33623316</a:EventNumber>
           <a:EventPlace i:nil="true" />
           <a:EventRegisteredDate>2016-11-09T00:00:00</a:EventRegisteredDate>
           <a:JudicialReasoning i:nil="true" />
           <a:RecordTerminationDecision i:nil="true" />
           <a:Term i:nil="true" />
           <a:TerminationForm>NotSpecified</a:TerminationForm>
           <a:TerminationTermDays i:nil="true" />
           <a:TerminationTermMonths i:nil="true" />
           <a:TerminationTermWeeks i:nil="true" />
           <a:TerminationTermYears i:nil="true" />
          </a:Event>
        </a:EventList>
        <a:ProcessCharacter>NotSpecified</a:ProcessCharacter>
        <a:ProcessEndDate>2016-11-07T00:00:00</a:ProcessEndDate>
        <a:ProcessStartDate>2016-06-27T00:00:00</a:ProcessStartDate>
        <a:RelatedPartyList>
          <a:RelatedParty>
           <a:BusinessStatus>NotSpecified</a:BusinessStatus>
           <a:CertificateIssueDate>2015-12-31T00:00:00</a:CertificateIssueDate>
           <a:CertificateNumber>00003</a:CertificateNumber>
           <a:Contacts>
            <a:Email>berzina@lawyer.lv</a:Email>
            <a:FixedLine i:nil="true" />
            <a:MobilePhone>29210665</a:MobilePhone>
           </a:Contacts>
           <a:miscellaneousId>2927848</a:miscellaneousId>
           <a:DecisionRegistrationDate i:nil="true" />
           <a:DecisionTerminationDate i:nil="true" />
           <a:IdNumber>090453-10901</a:IdNumber>
           <a:IdNumberType>PersonCode</a:IdNumberType>
           <a:InsolvencyRole>AdministratorChangeHistory</a:InsolvencyRole>
           <a:LegalForm>NotSpecified</a:LegalForm>
           <a:MainAddress>
            <a:ARCode i:nil="true" />
            <a:AddressLine i:nil="true" />
            <a:AddressSupplement i:nil="true" />
            <a:Country>NotSpecified</a:Country>
            <a:PostalCode i:nil="true" />
           </a:MainAddress>
           <a:Name>Aina Bērziņa</a:Name>
           <a:RegistrationDate>2016-07-28T00:00:00</a:RegistrationDate>
           <a:SecondaryAddress>
            <a:ARCode>100000000</a:ARCode>
            <a:AddressLine>Aleksandra Čaka iela 39-1, Rīga</a:AddressLine>
            <a:AddressSupplement i:nil="true" />
            <a:Country>NotSpecified</a:Country>
            <a:PostalCode>LV1011</a:PostalCode>
           </a:SecondaryAddress>
           <a:Status>Appointment</a:Status>
           <a:SubjectType>Individual</a:SubjectType>
           <a:TerminationDate i:nil="true" />
          </a:RelatedParty>
          <a:RelatedParty>
           <a:BusinessStatus>Active</a:BusinessStatus>
           <a:CertificateIssueDate i:nil="true" />
           <a:CertificateNumber i:nil="true" />
           <a:Contacts>
            <a:Email i:nil="true" />
            <a:FixedLine i:nil="true" />
            <a:MobilePhone i:nil="true" />
           </a:Contacts>
           <a:miscellaneousId>7592475</a:miscellaneousId>
           <a:DecisionRegistrationDate i:nil="true" />
           <a:DecisionTerminationDate i:nil="true" />
           <a:IdNumber>40001001342</a:IdNumber>
           <a:IdNumberType>RegistrationNumber</a:IdNumberType>
           <a:InsolvencyRole>Debitor</a:InsolvencyRole>
           <a:LegalForm>Farm</a:LegalForm>
           <a:MainAddress>
            <a:ARCode i:nil="true" />
            <a:AddressLine i:nil="true" />
            <a:AddressSupplement i:nil="true" />
            <a:Country>NotSpecified</a:Country>
            <a:PostalCode i:nil="true" />
           </a:MainAddress>
           <a:Name>Rīgas rajona Mālpils pagasta zemnieku saimniecība "MĀLPILS MAIZE"</a:Name>
           <a:RegistrationDate i:nil="true" />
           <a:SecondaryAddress>
            <a:ARCode>101309980</a:ARCode>
            <a:AddressLine>Mālpils nov., Mālpils, Ceriņu iela 2</a:AddressLine>
            <a:AddressSupplement i:nil="true" />
            <a:Country>NotSpecified</a:Country>
            <a:PostalCode i:nil="true" />
           </a:SecondaryAddress>
           <a:Status>NotSpecified</a:Status>
           <a:SubjectType>Company</a:SubjectType>
           <a:TerminationDate i:nil="true" />
          </a:RelatedParty>
        </a:RelatedPartyList>
        <a:StateOfInsolvencySolution>M08</a:StateOfInsolvencySolution>
        <a:TypeOfProcess>M</a:TypeOfProcess>
       </a:Insolvency>
      </a:InsolvencyList>
    </Insolvencies>
    <Liquidations i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Liquidations" />
    <NegativeContractList i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/NegativeContractList" />
    <NegativePaymentIncidentList i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/NegativePaymentIncidentList" />
    <Parameters>
      <Consent>true</Consent>
      <IDNumber>40001001342</IDNumber>
      <IDNumberType>RegistrationNumber</IDNumberType>
      <InquiryReason>CreditRiskManagement</InquiryReason>
      <ReportDate>2017-06-06T00:00:00</ReportDate>
      <Sections xmlns:a="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
       <a:string>Insolvencies</a:string>
      </Sections>
      <SubjectType>Company</SubjectType>
    </Parameters>
    <PaymentIncidentList i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/PaymentIncidentList" />
    <Pledges i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Pledges" />
    <ReportInfo xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/ReportInfo">
      <a:Created>2017-06-07T19:19:24.1073613+03:00</a:Created>
      <a:ReferenceNumber>10607039-7592475</a:ReferenceNumber>
      <a:ReportStatus>ReportGenerated</a:ReportStatus>
      <a:RequestedBy i:nil="true" />
      <a:Subscriber i:nil="true" />
      <a:Version>533</a:Version>
    </ReportInfo>
    <Restrictions i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/Restrictions" />
    <SubjectInfoHistory i:nil="true" xmlns:a="http://miscellaneous.com/CB5/v5.33/CustomReport/SubjectInfoHistory" />
   </CustomReport>
</Report>
</Reports>

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 1

This looks like relatively simple XML, you should just use XQuery to parse it
😎

Something like this:
SELECT REPORT.DATA.query('.') FROM @TXML.nodes('Reports/Report') AS REPORT(DATA)

thesqlcoder Mr or Mrs. 500 Points: 572 More actions · Answer 2

Eirikur Eiriksson - Wednesday, June 21, 2017 3:58 AM
This looks like relatively simple XML, you should just use XQuery to parse it
😎
Something like this:
SELECT REPORT.DATA.query('.') FROM @TXML.nodes('Reports/Report') AS REPORT(DATA)

Thanks, Let me dig further from this solution.

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 3

thesqlcoder - Wednesday, June 21, 2017 5:10 AM
Eirikur Eiriksson - Wednesday, June 21, 2017 3:58 AM
This looks like relatively simple XML, you should just use XQuery to parse it
😎
Something like this:
SELECT REPORT.DATA.query('.') FROM @TXML.nodes('Reports/Report') AS REPORT(DATA)
Thanks, Let me dig further from this solution.

You'll find several examples on this forum, feel free to ping back if you have any problems.
😎

thesqlcoder Mr or Mrs. 500 Points: 572 More actions · Answer 4

Eirikur Eiriksson - Wednesday, June 21, 2017 5:25 AM
thesqlcoder - Wednesday, June 21, 2017 5:10 AM
Eirikur Eiriksson - Wednesday, June 21, 2017 3:58 AM
This looks like relatively simple XML, you should just use XQuery to parse it
😎
Something like this:
SELECT REPORT.DATA.query('.') FROM @TXML.nodes('Reports/Report') AS REPORT(DATA)
Thanks, Let me dig further from this solution.
You'll find several examples on this forum, feel free to ping back if you have any problems.
😎

Thanks for your kind help, I'm looking for getting Insolvency(Data under <a:Insolvency>) data from above script but i can not.

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 5

Eirikur Eiriksson

SSC Guru

Points: 182912

June 21, 2017 at 7:22 am

#1947553

I'll have a look a little later
😎

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 6

thesqlcoder - Wednesday, June 21, 2017 6:12 AM
Eirikur Eiriksson - Wednesday, June 21, 2017 5:25 AM
thesqlcoder - Wednesday, June 21, 2017 5:10 AM
Eirikur Eiriksson - Wednesday, June 21, 2017 3:58 AM
This looks like relatively simple XML, you should just use XQuery to parse it
😎
Something like this:
SELECT REPORT.DATA.query('.') FROM @TXML.nodes('Reports/Report') AS REPORT(DATA)
Thanks, Let me dig further from this solution.
You'll find several examples on this forum, feel free to ping back if you have any problems.
😎
Thanks for your kind help, I'm looking for getting Insolvency(Data under <a:Insolvency>) data from above script but i can not.

Here is a quick example on how to extract the insolvency data
😎
SELECT REPORT.DATA.value('@IDNumber','VARCHAR(50)') AS IDNumber ,INSOLVENCY.DATA.value('(*:AccordingToLaw/text())[1]' ,'VARCHAR(50)') AS AccordingToLaw ,INSOLVENCY.DATA.value('(*:CreditorTermDays/text())[1]' ,'VARCHAR(50)') AS CreditorTermDays ,INSOLVENCY.DATA.value('(*:CreditorTermMonths/text())[1]' ,'VARCHAR(50)') AS CreditorTermMonths ,INSOLVENCY.DATA.value('(*:CreditorTermWeeks/text())[1]' ,'VARCHAR(50)') AS CreditorTermWeeks ,INSOLVENCY.DATA.value('(*:ProcessCharacter/text())[1]' ,'VARCHAR(50)') AS ProcessCharacter ,INSOLVENCY.DATA.value('(*:ProcessEndDate/text())[1]' ,'VARCHAR(50)') AS ProcessEndDate ,INSOLVENCY.DATA.value('(*:ProcessStartDate/text())[1]' ,'VARCHAR(50)') AS ProcessStartDate ,INSOLVENCY.DATA.value('(*:StateOfInsolvencySolution/text())[1]' ,'VARCHAR(50)') AS StateOfInsolvencySolution ,INSOLVENCY.DATA.value('(*:TypeOfProcess/text())[1]' ,'VARCHAR(50)') AS TypeOfProcess ,INSOLVENCY.DATA.query('.') AS INSOLVENCY_XML_DATA FROM @TXML.nodes('Reports/Report') AS REPORT(DATA) OUTER APPLY REPORT.DATA.nodes('*:CustomReport/*:Insolvencies/*:InsolvencyList/*:Insolvency') AS INSOLVENCY(DATA)

thesqlcoder Mr or Mrs. 500 Points: 572 More actions · Answer 7

Eirikur Eiriksson - Thursday, June 22, 2017 1:55 AM
thesqlcoder - Wednesday, June 21, 2017 6:12 AM
Eirikur Eiriksson - Wednesday, June 21, 2017 5:25 AM
thesqlcoder - Wednesday, June 21, 2017 5:10 AM
Eirikur Eiriksson - Wednesday, June 21, 2017 3:58 AM
This looks like relatively simple XML, you should just use XQuery to parse it
😎
Something like this:
SELECT REPORT.DATA.query('.') FROM @TXML.nodes('Reports/Report') AS REPORT(DATA)
Thanks, Let me dig further from this solution.
You'll find several examples on this forum, feel free to ping back if you have any problems.
😎
Thanks for your kind help, I'm looking for getting Insolvency(Data under <a:Insolvency>) data from above script but i can not.
Here is a quick example on how to extract the insolvency data
😎
SELECT REPORT.DATA.value('@IDNumber','VARCHAR(50)') AS IDNumber ,INSOLVENCY.DATA.value('(*:AccordingToLaw/text())[1]' ,'VARCHAR(50)') AS AccordingToLaw ,INSOLVENCY.DATA.value('(*:CreditorTermDays/text())[1]' ,'VARCHAR(50)') AS CreditorTermDays ,INSOLVENCY.DATA.value('(*:CreditorTermMonths/text())[1]' ,'VARCHAR(50)') AS CreditorTermMonths ,INSOLVENCY.DATA.value('(*:CreditorTermWeeks/text())[1]' ,'VARCHAR(50)') AS CreditorTermWeeks ,INSOLVENCY.DATA.value('(*:ProcessCharacter/text())[1]' ,'VARCHAR(50)') AS ProcessCharacter ,INSOLVENCY.DATA.value('(*:ProcessEndDate/text())[1]' ,'VARCHAR(50)') AS ProcessEndDate ,INSOLVENCY.DATA.value('(*:ProcessStartDate/text())[1]' ,'VARCHAR(50)') AS ProcessStartDate ,INSOLVENCY.DATA.value('(*:StateOfInsolvencySolution/text())[1]' ,'VARCHAR(50)') AS StateOfInsolvencySolution ,INSOLVENCY.DATA.value('(*:TypeOfProcess/text())[1]' ,'VARCHAR(50)') AS TypeOfProcess ,INSOLVENCY.DATA.query('.') AS INSOLVENCY_XML_DATA FROM @TXML.nodes('Reports/Report') AS REPORT(DATA) OUTER APPLY REPORT.DATA.nodes('*:CustomReport/*:Insolvencies/*:InsolvencyList/*:Insolvency') AS INSOLVENCY(DATA)

Many thanks sir..!! It worked like a charm..!!
Still one advice needed. If i have to store this is sql database, would that be advisable to store in a singlen table or i should create multiple tables.
And is it possible to get each <Report> output in one row(Or multiple rows)?