Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 & 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 & 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... |
Solved! Go to 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.
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.
You're a hero, PC2790 Impactful Individual!
That's it.
Found my way into XML in SQL and learned new things.
Thanx a lot!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.