From xml to table using sql server

Question

I needed to turn this xml from this:

          <Row>
            <Columns>
              <Column>
                <Value>BB1</Value>
                <Name>Location</Name>
              </Column>
              <Column>
                <Value>1593338</Value>
                <Name>Location_Key</Name>
              </Column>
              <Column>
                <Value>0</Value>
                <Name>Quantity</Name>
              </Column>
            </Columns>
          </Row>
          <Row>
            <Columns>
              <Column>
                <Value>DR11 40</Value>
                <Name>Location</Name>
              </Column>
              <Column>
                <Value>1593251</Value>
                <Name>Location_Key</Name>
              </Column>
              <Column>
                <Value>0</Value>
                <Name>Quantity</Name>
              </Column>
            </Columns>
          </Row>

To this table with these columns and rows

(Location)  (Location_Key)  (Quantity)
BB1         1593338            0 
DR11        1593251            0

Using Sql Server

I have been trying for weeks but to no avail. Any answer would be greatly aprreciated.

Does the table already exist in your database or are you trying to create the table automatically, then add rows to it? — David
– David, Commented Jan 2, 2014 at 18:14

marc_s · Accepted Answer · 2014-01-02 18:22:42Z

2

Try this:

DECLARE @input XML = '<Row>
            <Columns>
              <Column>
                <Value>BB1</Value>
                <Name>Location</Name>
              </Column>
              <Column>
                <Value>1593338</Value>
                <Name>Location_Key</Name>
              </Column>
              <Column>
                <Value>0</Value>
                <Name>Quantity</Name>
              </Column>
            </Columns>
          </Row>
          <Row>
            <Columns>
              <Column>
                <Value>DR11 40</Value>
                <Name>Location</Name>
              </Column>
              <Column>
                <Value>1593251</Value>
                <Name>Location_Key</Name>
              </Column>
              <Column>
                <Value>0</Value>
                <Name>Quantity</Name>
              </Column>
            </Columns>
          </Row>'

SELECT
    Location = xc.value('(Column[Name="Location"]/Value)[1]', 'varchar(20)'),
    LocationKey = xc.value('(Column[Name="Location_Key"]/Value)[1]', 'varchar(20)'),
    Quantity = xc.value('(Column[Name="Quantity"]/Value)[1]', 'int')
FROM 
    @input.nodes('/Row/Columns') AS XT(XC)

This gives you an output something like this:

enter image description here

You can use this SELECT inside an INSERT INTO ..... or do whatever you like to do with it.

answered Jan 2, 2014 at 18:22

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cahillsiuc Over a year ago

I also needed to know how/where to declare the namespaces:<br>

<?xml version="1.0" encoding="utf-8"?> <soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">   <soap:Body>     <ExecuteDataSourceResponse xmlns="http://www.plexus-online.com/DataSource">

Collectives™ on Stack Overflow

From xml to table using sql server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related