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

Question

Who can help me with the following?&nbsp;In a SQL-database the table Invoice contains the field Invoice, with multiple rows in XML-information.&nbsp;My question: How can I extract the column as a separate XML-file?&nbsp;Additional: The information in every row in Power BI is cut off at 1024 characters. I hope this will be solved together&nbsp; with the solution to question 1.&nbsp;The information looks like this:&nbsp;InvoiceIDCustomerIDInvoiceEFF11184-8E4D-4DF2-BAFD-000396BC1357588DD764-4A93-439A-A272-3CD7253BC489&lt;Invoice&gt;&lt;Id&gt;EFF11184-8E4D-4DF2-BAFD-000396BC1357&lt;/Id&gt;&lt;Customer&gt;Tenax Tree-Plugs &amp;amp; Seeds B.V.&lt;/Customer&gt;&lt;CustomerId&gt;588DD764-4A93-439A-A272-3CD7253BC489&lt;/CustomerId&gt;&lt;SubtotalsOnInvoice&gt;0&lt;/SubtotalsOnInvoice&gt;&lt;TaxExempt&gt;0&lt;/TaxExempt&gt;&lt;ConsolidateBilling&gt;0&lt;/ConsolidateBilling&gt;&lt;PaymentTerms&gt;14&lt;/PaymentTerms&gt;&lt;VatNumber&gt;NL8591.26.067.B01&lt;/VatNumber&gt;&lt;DebtorCode&gt;29683857&lt;/DebtorCode&gt;&lt;NameOfDepartmentToBill&gt;Tenax Tree-Plugs &amp;amp; Seeds B.V.&lt;/NameOfDepartmentToBill&gt;&lt;SpecialCountry&gt;0&lt;/SpecialCountry&gt;&lt;StateAcronim /&gt;&lt;Tier3Tier4SameCountry&gt;1&lt;/Tier3Tier4SameCountry&gt;&lt;Street&gt;Overasebaan 11&lt;/Street&gt;&lt;City&gt;RIJSBERGEN&lt;/City&gt;&lt;Zipcode&gt;4891 RG&lt;/Zipcode&gt;&lt;InvoiceDate&gt;2021-01-27&lt;/InvoiceDate&gt;&lt;Country&gt;Netherlands&lt;/Country&gt;&lt;IsIncasso&gt;0&lt;/IsIncasso&gt;&lt;BankAccountNumber&gt;NL58RABO0160531683&lt;/BankAccountNumber&gt;&lt;InvoiceNumber&gt;1118-20210127-0-1783&lt;/InvoiceNumber&gt;&lt;BillingEntity&gt;C4666CB7-CA5F-401D-8EBC-BF58A142B9F1&lt;/BillingEntity&gt;Daemen ICT&lt;SubsidiaryData&gt;&lt;Subsidiary&gt;&lt;SubsidiaryId&gt;588DD764-4A93-439A-A272-3CD7253BC489&lt;/SubsidiaryId&gt;&lt;Subsidiary...81D4ADBE-540C-4D12-A803-00478A409049EE938FCA-5262-4222-90F3-DF283A2CDB90&lt;Invoice&gt;&lt;Id&gt;81D4ADBE-540C-4D12-A803-00478A409049&lt;/Id&gt;&lt;Customer&gt;iDtools&lt;/Customer&gt;&lt;CustomerId&gt;EE938FCA-5262-4222-90F3-DF283A2CDB90&lt;/CustomerId&gt;&lt;TaxExempt&gt;0&lt;/TaxExempt&gt;&lt;DebtorCode&gt;29713369&lt;/DebtorCode&gt;&lt;NameOfDepartmentToBill&gt;iDtools&lt;/NameOfDepartmentToBill&gt;&lt;SpecialCountry&gt;0&lt;/SpecialCountry&gt;&lt;StateAcronim /&gt;&lt;Tier3Tier4SameCountry&gt;1&lt;/Tier3Tier4SameCountry&gt;&lt;Street&gt;Pennendijk 9&lt;/Street&gt;&lt;City&gt;Ulvenhout&lt;/City&gt;&lt;Zipcode&gt;4851 VB&lt;/Zipcode&gt;&lt;InvoiceDate&gt;2020-12-02&lt;/InvoiceDate&gt;&lt;Country&gt;Netherlands&lt;/Country&gt;&lt;IsIncasso&gt;1&lt;/IsIncasso&gt;&lt;BankAccountNumber&gt;NL45RABO0143632868&lt;/BankAccountNumber&gt;&lt;InvoiceNumber&gt;857-20201202-0-1361&lt;/InvoiceNumber&gt;&lt;BillingEntity&gt;C4666CB7-CA5F-401D-8EBC-BF58A142B9F1&lt;/BillingEntity&gt;Default billing entity&lt;InvoiceItems&gt;&lt;InvoiceItem&gt;&lt;ContractName&gt;Daemen ICT VDI omgeving&lt;/ContractName&gt;&lt;ProductName&gt;Daemen ICT VDI omgeving per gebruiker&lt;/ProductName&gt;&lt;Ledger&gt;80007&lt;/Ledger&gt;&lt;LedgerDescription&gt;Omzet cloud diensten&lt;/LedgerDescription&gt;&lt;BrutoPricePerItem&gt;49.00&lt;/BrutoPricePerItem&gt;&lt;Amount&gt;2.00&lt;/Amount&gt;&lt;Discount&gt;0.00...&nbsp;&nbsp;

pc2790 · Accepted Answer

Hello&nbsp;haraldjw&nbsp;,&nbsp;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.

Forum Discussion

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

2 Replies

Related content

Moving Mirrored SQL database between workspace

Fabric SQL Database SQL endpoint is not refreshing

SQL Database (preview)

Workspace Identity with Azure SQL Databases

SQL Database - Source Control

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