Loading XML fields into Table columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Loading XML fields into Table columns

Author

Topic

byomjan
Starting Member

34 Posts

Posted - 2011-12-08 : 01:14:36

I Want to convert these xml values into Columns.

<?xml version="1.0" encoding="utf-8" ?>
<Store Name="Peacock" StoreID="01" >
<Sales>
<Sale SaleID="1000000000000000000000" TransactionDate="" TransactionNumber="">
<Payment Event="Payment" EventDate="20111110 18:00:0008:00">
<Reference>0004676412</Reference>
<Merchant>JOHN</Merchant>
<Amount>10.00</Amount>
<PaymentType/>

</Payment >
</Sale>

</Sales>

</Store>

-------------
SELECT
Store.x.value('(@Name)', 'VARCHAR(100)') As Name,
Store.x.value('(@StoreID)', 'VARCHAR(100)') As StoreID,
Sale.x.value('(@SaleID)[1]', 'VARCHAR(max)') As SaleID,
Sale.x.value('(@TransactionDate)[1]', 'VARCHAR(max)') As TransactionDate,
Sale.x.value('(@TransactionNumber)[1]', 'VARCHAR(max)') As TransactionNumber,
Payment.x.value('(@Event)', 'VARCHAR(100)') As Event,
Payment.x.value('(@EventDate)', 'VARCHAR(100)') As EventDate,
payment.x.value('(Reference)[1]', 'VARCHAR(100)') As Reference,
payment.x.value('(Merchant)[1]', 'VARCHAR(100)') As Merchant
FROM ( SELECT CAST(x AS XML)
FROM
OPENROWSET( BULK 'C:\Test.xml',SINGLE_BLOB) AS T(x))
AS T(x)
CROSS APPLY x.nodes('./*') AS Store(x)
CROSS APPLY Store.x.nodes('./*/*') AS Sale(x)
CROSS APPLY Sale.x.nodes('./*') AS Payment(x)

The problem is that if i give the node names , it does work . But ( *) works. Also I am able to retrive the Payment Nodes ( event and Event name) , But not able to retrive Merchant, reference and Amount.

Can some one please help.

Byomjan....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-08 : 01:31:57

try like below


SELECT 
t.u.value('(../@Name)', 'VARCHAR(100)') As Name, 
t.u.value('(../@StoreID)', 'VARCHAR(100)') As StoreID,
t.u.value('(./Sale/@SaleID)[1]', 'VARCHAR(max)') As SaleID,
t.u.value('(./Sale/@TransactionDate)[1]', 'VARCHAR(max)') As TransactionDate,
t.u.value('(./Sale/@TransactionNumber)[1]', 'VARCHAR(max)') As TransactionNumber,
t.u.value('(./Sale/Payment/@Event)[1]', 'VARCHAR(100)') As Event,
t.u.value('(./Sale/Payment/@EventDate)[1]', 'VARCHAR(100)') As EventDate, 
t.u.value('(./Sale/Payment/Reference)[1]', 'VARCHAR(100)') As Reference,
t.u.value('(./Sale/Payment/Merchant)[1]', 'VARCHAR(100)') As Merchant,
t.u.value('(./Sale/Payment/Amount)[1]', 'decimal(10,2)') As Amount
FROM ( SELECT CAST(x AS XML)
FROM
OPENROWSET( BULK 'C:\Test.xml',SINGLE_BLOB) AS T(x))
AS T(x)
CROSS APPLY x.nodes('/Store/Sales') AS t(u)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

byomjan
Starting Member

34 Posts

Posted - 2011-12-08 : 01:58:11

CROSS APPLY x.nodes('/Store/Sales') AS t(u)

Some how It is not recognizing the node values. So i am using *

Then i tried Using the
t.u.value('(../@Name)', 'VARCHAR(100)') As Name..

I got the values of first nodes by using signle (.) that is name and Store ID.
But I am not able to proceed to next level. Either by using ./Sale/ nor bt ././

Can you also tell me the significance of . and ..

Byomjan....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-08 : 02:00:49

. means current level and .. means just the previous (parent) level

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-08 : 02:03:21

this is working for me


SELECT 
t.u.value('(../@Name)', 'VARCHAR(100)') As Name, 
t.u.value('(../@StoreID)', 'VARCHAR(100)') As StoreID,
t.u.value('(./Sale/@SaleID)[1]', 'VARCHAR(max)') As SaleID,
t.u.value('(./Sale/@TransactionDate)[1]', 'VARCHAR(max)') As TransactionDate,
t.u.value('(./Sale/@TransactionNumber)[1]', 'VARCHAR(max)') As TransactionNumber,
t.u.value('(./Sale/Payment/@Event)[1]', 'VARCHAR(100)') As Event,
t.u.value('(./Sale/Payment/@EventDate)[1]', 'VARCHAR(100)') As EventDate, 
t.u.value('(./Sale/Payment/Reference)[1]', 'VARCHAR(100)') As Reference,
t.u.value('(./Sale/Payment/Merchant)[1]', 'VARCHAR(100)') As Merchant,
t.u.value('(./Sale/Payment/Amount)[1]', 'decimal(10,2)') As Amount
FROM 

( SELECT CAST(x AS XML)
FROM
OPENROWSET( BULK 'C:\Documents and Settings\visakh_murukesan\Desktop\test.xml',SINGLE_BLOB) AS k(x))
AS k(x)
CROSS APPLY x.nodes('/Store/Sales') AS t(u)

i tried putting file in my desktop and it worked

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

byomjan
Starting Member

34 Posts

Posted - 2011-12-08 : 02:06:10

1)what can be the reason CROSS APPLY x.nodes('/Store/Sales') is not working ? I am forced to use *, which is giving some data for thr first level values.
2)When i m using * what does it signify ?

Byomjan....

byomjan
Starting Member

34 Posts

Posted - 2011-12-08 : 02:27:07

Ok .After removing DTD it could recognize .Thanks Visakh.

The 2 bottom levels are dynamnic.
In this case when the node is payment, it has set of nodes and if the node is refund , it has different set of nodes.
Can i put condition.Exixtence of a node value?

Byomjan....

byomjan
Starting Member

34 Posts

Posted - 2011-12-08 : 03:28:45

./Sale/@SaleID)[1] -> this is for only 1 record..
When XML contains multiple records , then ?

Byomjan....

byomjan
Starting Member

34 Posts

Posted - 2011-12-08 : 03:58:56

Vishakh, please help..how can i have all the records...its just select 1 ?

Byomjan....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-08 : 07:02:22

please post your full xml document. without seeing it, its difficult to suggest

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

byomjan
Starting Member

34 Posts

Posted - 2011-12-08 : 12:04:44

Visakh, The same XML that i have posted....and it worked...But what i am asking is when i m using [1] , it is giving me first record. But the XML file can contain multiple records..In that case what i will do ?

Byomjan....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-08 : 12:32:15

quote:
Originally posted by byomjan

Visakh, The same XML that i have posted....and it worked...But what i am asking is when i m using [1] , it is giving me first record. But the XML file can contain multiple records..In that case what i will do ?

Byomjan....

you mean multiple sale nodes?
then use like


SELECT 
t.u.value('(//@Name)[1]', 'VARCHAR(100)') As Name, 
t.u.value('(//@StoreID)[1]', 'VARCHAR(100)') As StoreID,
t.u.value('(./@SaleID)[1]', 'VARCHAR(max)') As SaleID,
t.u.value('(./@TransactionDate)[1]', 'VARCHAR(max)') As TransactionDate,
t.u.value('(./@TransactionNumber)[1]', 'VARCHAR(max)') As TransactionNumber,
t.u.value('(./Payment/@Event)[1]', 'VARCHAR(100)') As Event,
t.u.value('(./Payment/@EventDate)[1]', 'VARCHAR(100)') As EventDate, 
t.u.value('(./Payment/Reference)[1]', 'VARCHAR(100)') As Reference,
t.u.value('(./Payment/Merchant)[1]', 'VARCHAR(100)') As Merchant,
t.u.value('(./Payment/Amount)[1]', 'decimal(10,2)') As Amount
FROM 

( SELECT CAST(x AS XML)
FROM
OPENROWSET( BULK '<your actual path>\Test1.xml',SINGLE_BLOB) AS k(x))
AS k(x)
CROSS APPLY x.nodes('/Store/Sales/Sale') AS t(u)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

byomjan
Starting Member

34 Posts

Posted - 2011-12-08 : 12:54:25

Sorry for the miscomuunication..

I meant the XML like ..

<?xml version="1.0" encoding="utf-8" ?>
<Store Name="Peacock" StoreID="01" >
<Sales>
<Sale SaleID="1000000000000000000000" TransactionDate="" TransactionNumber="">
<Payment Event="Payment" EventDate="20111110 18:00:0008:00">
<Reference>0004676412</Reference>
<Merchant>JOHN</Merchant>
<Amount>10.00</Amount>
<PaymentType/>

</Payment >
</Sale>
-- new sale node

<Sale SaleID="200000000" TransactionDate="20110123" TransactionNumber="">
<Payment Event="Payment" EventDate="20110110 18:00:0008:00">
<Reference>0004699622</Reference>
<Merchant>MARY</Merchant>
<Amount>20.00</Amount>
<PaymentType/>

</Payment >
</Sale>
-----> 1000s of <sale> nodes like this..
</Sales>

</Store>

Byomjan....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-08 : 12:58:44

thats same i gave in my last suggestion.
try it out first

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources