How to extract XML-information from a column in SQL-database

[haraldjw]

Who can help me with the following?

In a SQL-database the table Invoice contains the field Invoice, with multiple rows in XML-information.

My question: How can I extract the column as a separate XML-file?

Additional: The information in every row in Power BI is cut off at 1024 characters. I hope this will be solved together  with the solution to question 1.

The information looks like this:

InvoiceID	CustomerID	Invoice
EFF11184-8E4D-4DF2-BAFD-000396BC1357	588DD764-4A93-439A-A272-3CD7253BC489	<Invoice><Id>EFF11184-8E4D-4DF2-BAFD-000396BC1357</Id><Customer>Tenax Tree-Plugs &amp; Seeds B.V.</Customer><CustomerId>588DD764-4A93-439A-A272-3CD7253BC489</CustomerId><SubtotalsOnInvoice>0</SubtotalsOnInvoice><TaxExempt>0</TaxExempt><ConsolidateBilling>0</ConsolidateBilling><PaymentTerms>14</PaymentTerms><VatNumber>NL8591.26.067.B01</VatNumber><DebtorCode>29683857</DebtorCode><NameOfDepartmentToBill>Tenax Tree-Plugs &amp; Seeds B.V.</NameOfDepartmentToBill><SpecialCountry>0</SpecialCountry><StateAcronim /><Tier3Tier4SameCountry>1</Tier3Tier4SameCountry><Street>Overasebaan 11</Street><City>RIJSBERGEN</City><Zipcode>4891 RG</Zipcode><InvoiceDate>2021-01-27</InvoiceDate><Country>Netherlands</Country><IsIncasso>0</IsIncasso><BankAccountNumber>NL58RABO0160531683</BankAccountNumber><InvoiceNumber>1118-20210127-0-1783</InvoiceNumber><BillingEntity>C4666CB7-CA5F-401D-8EBC-BF58A142B9F1</BillingEntity>Daemen ICT<SubsidiaryData><Subsidiary><SubsidiaryId>588DD764-4A93-439A-A272-3CD7253BC489</SubsidiaryId><Subsidiary...
81D4ADBE-540C-4D12-A803-00478A409049	EE938FCA-5262-4222-90F3-DF283A2CDB90	<Invoice><Id>81D4ADBE-540C-4D12-A803-00478A409049</Id><Customer>iDtools</Customer><CustomerId>EE938FCA-5262-4222-90F3-DF283A2CDB90</CustomerId><TaxExempt>0</TaxExempt><DebtorCode>29713369</DebtorCode><NameOfDepartmentToBill>iDtools</NameOfDepartmentToBill><SpecialCountry>0</SpecialCountry><StateAcronim /><Tier3Tier4SameCountry>1</Tier3Tier4SameCountry><Street>Pennendijk 9</Street><City>Ulvenhout</City><Zipcode>4851 VB</Zipcode><InvoiceDate>2020-12-02</InvoiceDate><Country>Netherlands</Country><IsIncasso>1</IsIncasso><BankAccountNumber>NL45RABO0143632868</BankAccountNumber><InvoiceNumber>857-20201202-0-1361</InvoiceNumber><BillingEntity>C4666CB7-CA5F-401D-8EBC-BF58A142B9F1</BillingEntity>Default billing entity<InvoiceItems><InvoiceItem><ContractName>Daemen ICT VDI omgeving</ContractName><ProductName>Daemen ICT VDI omgeving per gebruiker</ProductName><Ledger>80007</Ledger><LedgerDescription>Omzet cloud diensten</LedgerDescription><BrutoPricePerItem>49.00</BrutoPricePerItem><Amount>2.00</Amount><Discount>0.00...

[PC2790 · Accepted solution]

Hello @haraldjw ,

Try parsing the Invoice column as xml in Power Query Editor as below:

You can then expan the column and select the attributes as per your requirement as below:

Hope this works for you.

[haraldjw]

You're a hero, PC2790 Impactful Individual!

That's it.

Found my way into XML in SQL and learned new things.

Thanx a lot!