SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Inserting data using OPENXML


Inserting data using OPENXML

Author
Message
thesqlcoder
thesqlcoder
SSC-Addicted
SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)

Group: General Forum Members
Points: 491 Visits: 138
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>
<aBigGrinateCompletion i:nil="true" />
<aBigGrinateRecordRegistered>2016-07-28T00:00:00</aBigGrinateRecordRegistered>
<aBigGrinateRecordTerminated i:nil="true" />
<aBigGrinescription 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>
<aBigGrinateCompletion i:nil="true" />
<aBigGrinateRecordRegistered>2016-07-28T00:00:00</aBigGrinateRecordRegistered>
<aBigGrinateRecordTerminated i:nil="true" />
<aBigGrinescription 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>
<aBigGrinateCompletion i:nil="true" />
<aBigGrinateRecordRegistered>2016-11-09T00:00:00</aBigGrinateRecordRegistered>
<aBigGrinateRecordTerminated i:nil="true" />
<aBigGrinescription 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>
<aTonguerocessCharacter>NotSpecified</aTonguerocessCharacter>
<aTonguerocessEndDate>2016-11-07T00:00:00</aTonguerocessEndDate>
<aTonguerocessStartDate>2016-06-27T00:00:00</aTonguerocessStartDate>
<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>
<aBigGrinecisionRegistrationDate i:nil="true" />
<aBigGrinecisionTerminationDate 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>
<aTongueostalCode 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>
<aTongueostalCode>LV1011</aTongueostalCode>
</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>
<aBigGrinecisionRegistrationDate i:nil="true" />
<aBigGrinecisionTerminationDate 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>
<aTongueostalCode 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>
<aTongueostalCode 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>
Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)

Group: General Forum Members
Points: 159119 Visits: 23308
This looks like relatively simple XML, you should just use XQuery to parse it
Cool

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

thesqlcoder
thesqlcoder
SSC-Addicted
SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)

Group: General Forum Members
Points: 491 Visits: 138
Eirikur Eiriksson - Wednesday, June 21, 2017 3:58 AM
This looks like relatively simple XML, you should just use XQuery to parse it
Cool

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
Eirikur Eiriksson
SSC Guru
SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)

Group: General Forum Members
Points: 159119 Visits: 23308
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
Cool

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.
Cool

thesqlcoder
thesqlcoder
SSC-Addicted
SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)

Group: General Forum Members
Points: 491 Visits: 138
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
Cool

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.
Cool

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

Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)

Group: General Forum Members
Points: 159119 Visits: 23308
I'll have a look a little later
Cool
Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)

Group: General Forum Members
Points: 159119 Visits: 23308
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
Cool

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.
Cool

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

Here is a quick example on how to extract the insolvency data
Cool
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('(*TonguerocessCharacter/text())[1]' ,'VARCHAR(50)') AS ProcessCharacter
,INSOLVENCY.DATA.value('(*TonguerocessEndDate/text())[1]' ,'VARCHAR(50)') AS ProcessEndDate
,INSOLVENCY.DATA.value('(*TonguerocessStartDate/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
thesqlcoder
SSC-Addicted
SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)

Group: General Forum Members
Points: 491 Visits: 138
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
Cool

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.
Cool

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

Here is a quick example on how to extract the insolvency data
Cool
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('(*TonguerocessCharacter/text())[1]' ,'VARCHAR(50)') AS ProcessCharacter
,INSOLVENCY.DATA.value('(*TonguerocessEndDate/text())[1]' ,'VARCHAR(50)') AS ProcessEndDate
,INSOLVENCY.DATA.value('(*TonguerocessStartDate/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)?

Go


Permissions

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

Select a forum








































































































































































SQLServerCentral


Search