Read xml data into a SQL table

Question

I want to read the xml string and push the data into a SQL table. I have this data in a SQL variable

<Policy>
   <No>
       <old>1</old>
       <new>2</new>
   </No>
   <name>
       <old>xxx</old>
       <new>yyy</new>
   </name>
   <amount>
       <old>1000</old>
       <new>1500</new>
   </amount>
</Policy>
<Policy>
    <No>
        <old>3</old>
        <new>4</new>
    </No>
    <name>
        <old>aaa</old>
        <new>bbb</new>
    </name>
    <amount>
        <old>2000</old>
        <new>2500</new>
    </amount>
</Policy>
<Policy>
    <No>
        <old>5</old>
        <new>6</new>
    </No>
    <name>
        <old>qqq</old>
        <new>www</new>
    </name>
    <amount>
        <old>1000</old>
        <new>1500</new>
    </amount>
</Policy>

I want to interpret the data as old & new

like

OLD

No   Name amount
-----------------    
1    xxx  1000
3    aaa  2000
5    qqq  1000

New

No   Name amount
----------------
2    yyy  1500
4    bbb  2500
6    www  1500

Thanks,

Kihtrak J

If you want to we help you, You need to be more specific e.g. What typ of SQL and whic version are you using, How you want to accompish this by mking app or using sql by itself, From where there XML comming, resident in table or from client. Who is going to make translation Clinet or server, what have you tierd so far etc — adopilot
– adopilot, Commented Nov 30, 2014 at 10:30
SQL is just a query language - you don't have tables in SQL - you tables in a DATABASE that uses SQL - but you're not telling us WHAT database you're using. Please add relevent tags (Oracle, SQL Server, DB2, MySQL, PostgreSQL) to your question! — marc_s
– marc_s, Commented Nov 30, 2014 at 10:42
Im trying to read the data from the xml variabel in SQL server 2008 R2. SELECT * FROM @xml.nodes('/Policy') Tab(Col) — Kihtrak J
– Kihtrak J, Commented Nov 30, 2014 at 10:43

marc_s · Accepted Answer · 2014-11-30 10:58:27Z

2

Assuming you have this XML in a SQL Server variable called @XmlData, you can use these native XQuery expressions to get what you're looking for:

-- select "old" data
SELECT
    OldNo = XC.value('(No/old)[1]', 'int'),
    OldName = XC.value('(name/old)[1]', 'varchar(25)'),
    OldAmount = XC.value('(amount/old)[1]', 'decimal(20,2)')
FROM 
    @XmlData.nodes('/Policy') AS XT(XC)

-- select "new" data
SELECT
    NewNo = XC.value('(No/new)[1]', 'int'),
    NewName = XC.value('(name/new)[1]', 'varchar(25)'),
    NewAmount = XC.value('(amount/new)[1]', 'decimal(20,2)')
FROM 
    @XmlData.nodes('/Policy') AS XT(XC)

answered Nov 30, 2014 at 10:58

marc_s

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

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

Comments

peter.petrov · Accepted Answer · 2014-11-30 10:56:34Z

You need to have only 1 root (top-level) element in your XML document.

Then... try doing something like this.

DECLARE @DocHandle int
DECLARE @XmlDocument nvarchar(max)
SET @XmlDocument = N'<ROOT><Policy>
   <No>
       <old>1</old>
       <new>2</new>
   </No>
   <name>
       <old>xxx</old>
       <new>yyy</new>
   </name>
   <amount>
       <old>1000</old>
       <new>1500</new>
   </amount>
</Policy>
<Policy>
    <No>
        <old>3</old>
        <new>4</new>
    </No>
    <name>
        <old>aaa</old>
        <new>bbb</new>
    </name>
    <amount>
        <old>2000</old>
        <new>2500</new>
    </amount>
</Policy>
<Policy>
    <No>
        <old>5</old>
        <new>6</new>
    </No>
    <name>
        <old>qqq</old>
        <new>www</new>
    </name>
    <amount>
        <old>1000</old>
        <new>1500</new>
    </amount>
</Policy></ROOT>'


-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument

-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@DocHandle, '/ROOT/Policy/No', 2)
      WITH 
    (
        no_old varchar(200) 'old',
        no_new varchar(200) 'new',
        name_old varchar(200) '../name/old',
        name_new varchar(200) '../name/new',
        amount_old varchar(200) '../amount/old',
        amount_new varchar(200) '../amount/new'
    )

--- Clean up the internal representation.
EXEC sp_xml_removedocument @DocHandle

For more details check out these two MSDN pages:

OPENXML

OPENXML examples

Collectives™ on Stack Overflow

Read xml data into a SQL table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related