CODEWITHSUNDEEP
sqlsql-serverxmlxml-parsing
theAccountant.py
theAccountant.py

Reputation: 141

split xml column into multiple columns

I need to split a column with into multiple columns using sql statement. This column saved in "dbo.ItemsEbay" table as "ItemSpecifics"

Here is the example of one xml record from ItemSpecifics column:

 <SelectedValues haveDefaultsBeenSet="true">
<SelectedValue><Name>Consignment Status</Name><Value>False</Value></SelectedValue>
<SelectedValue><Name>Salesperson</Name><Value>'Motya'</Value></SelectedValue>
 <SelectedValue><Name>Last Cycle Counted</Name><Value>1/1/1990</Value></SelectedValue>
<SelectedValue><Name>CycleCountError</Name><Value>No</Value></SelectedValue>
</SelectedValues>

The output for the query should should have four columns formatted as follows :

ConsimgentStatus --Boolean

Salesperson --String

Last Cycle Counted --date

CycleCountError--string

Upvotes: 2

Views: 6612

Answers (1)

har07
har07

Reputation: 89325

Look into SQL Server XML method value()

SELECT 
    i.ItemSpecifics.value('(/*/SelectedValue[Name="Consignment Status"]/Value)[1]', 'bit') 
            as ConsimgentStatus
    , i.ItemSpecifics.value('(/*/SelectedValue[Name="Salesperson"]/Value)[1]', 'varchar(500)') 
            as Salesperson
    , i.ItemSpecifics.value('(/*/SelectedValue[Name="Last Cycle Counted"]/Value)[1]', 'date') 
            as LastCycleCounted
    , i.ItemSpecifics.value('(/*/SelectedValue[Name="CycleCountError"]/Value)[1]', 'varchar(500)') 
            as CycleCountError
FROM dbo.ItemsEbay i

Demo

Side note : /*/ is used above rather than /SelectedValues/ just to shorten the query so it's visible in single line. You can also use // but the query would be a bit less efficient.

Upvotes: 3

Related Questions