XML file - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

XML file

Author

Topic

suman.reddy39
Starting Member

34 Posts

Posted - 2009-01-09 : 03:59:10

hi Gurus,
i need a small help. i got an XML file in which i have data which i have to deploy that data into a table, which is in sqlserver2005.i have already created a table. And i need to retrive that data again can any one help me in this regards,

the table is

CREATE TABLE KHAN(

customer_id Varchar(50) NOT NULL,
contact_name Varchar(50) NULL,
order_date DATETIME NULL,
order_id Varchar(50) NULL,
product_id Varchar(50) NULL,
quantity DECIMAL(18,4) NULL,

);
GO

this is the XML file

<root>
<customer customer_id="JH01" contact_name="John harriot">
<order order_id="1001" customer_id="JH01" order_date="2006-07-04">
<orderdetail product_id="11" quantity="12"/>
<orderdetail product_id="22" quantity="10"/>
</order>
</customer>
<customer customer_id="GH02" contact_name="khan harriot">
<order order_id="1002" customer_id="GH02" order_date="2006-07-05">
<orderdetail product_id="101" quantity="120"/>
<orderdetail product_id="202" quantity="100"/>
</order>
</customer>

Don't go the way Life takes you, Take the Life the way you go

subbi

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-09 : 04:15:34

[code]DECLARE @data XML

SET @data = '<root>
<customer customer_id="JH01" contact_name="John harriot">
<order order_id="1001" customer_id="JH01" order_date="2006-07-04">
<orderdetail product_id="11" quantity="12"/>
<orderdetail product_id="22" quantity="10"/>
</order>
</customer>
<customer customer_id="GH02" contact_name="khan harriot">
<order order_id="1002" customer_id="GH02" order_date="2006-07-05">
<orderdetail product_id="101" quantity="120"/>
<orderdetail product_id="202" quantity="100"/>
</order>
</customer>
</root>'

SELECT c.value('@customer_id', 'VARCHAR(50)') AS CustomerID,
c.value('@contact_name', 'VARCHAR(50)') AS ContactName,
o.value('@order_date', 'DATETIME') AS OrderDate,
o.value('@order_id', 'VARCHAR(50)') AS OrderID,
od.value('@product_id', 'VARCHAR(50)') AS ProductID,
od.value('@quantity', 'DECIMAL(18, 4)') AS Quantity
FROM @data.nodes('/root/customer') AS r(c)
CROSS APPLY c.nodes('order') AS c(o)
CROSS APPLY o.nodes('orderdetail') AS o(od)[/code]

E 12°55'05.63"
N 56°04'39.26"

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-09 : 12:50:57

quote:

Originally posted by Peso

DECLARE	@data XML

SET	@data =	'<root>
			<customer customer_id="JH01" contact_name="John harriot">
				<order order_id="1001" customer_id="JH01" order_date="2006-07-04">
					<orderdetail product_id="11" quantity="12"/>
					<orderdetail product_id="22" quantity="10"/>
				</order>
			</customer>
			<customer customer_id="GH02" contact_name="khan harriot">
				<order order_id="1002" customer_id="GH02" order_date="2006-07-05">
					<orderdetail product_id="101" quantity="120"/>
					<orderdetail product_id="202" quantity="100"/>
				</order>
			</customer>
		</root>'

SELECT		c.value('@customer_id', 'VARCHAR(50)') AS CustomerID,
		c.value('@contact_name', 'VARCHAR(50)') AS ContactName,
		o.value('@order_date', 'DATETIME') AS OrderDate,
		o.value('@order_id', 'VARCHAR(50)') AS OrderID,
		od.value('@product_id', 'VARCHAR(50)') AS ProductID,
		od.value('@quantity', 'DECIMAL(18, 4)') AS Quantity
FROM		@data.nodes('/root/customer') AS r(c)
CROSS APPLY	c.nodes('order') AS c(o)
CROSS APPLY	o.nodes('orderdetail') AS o(od)

E 12°55'05.63"
N 56°04'39.26"

This won't work in SQL 2000.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-09 : 12:54:46

My bad. I didn't see this was 2000 forum.
Will give another shot on monday.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-09 : 13:35:32

quote:
Originally posted by Peso

My bad. I didn't see this was 2000 forum.
Will give another shot on monday.

E 12°55'05.63"
N 56°04'39.26"

Nope you're lucky

OP has told he's using SQL 2005 though he has posted this in 2000 forum

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-09 : 14:04:56

this will be sql 2000 equivalent


DECLARE @idoc int
DECLARE @doc varchar(5000)
SET @doc ='
<root>
<customer customer_id="JH01" contact_name="John harriot">
<order order_id="1001" customer_id="JH01" order_date="2006-07-04">
<orderdetail product_id="11" quantity="12"/>
<orderdetail product_id="22" quantity="10"/>
</order>
</customer>
<customer customer_id="GH02" contact_name="khan harriot">
<order order_id="1002" customer_id="GH02" order_date="2006-07-05">
<orderdetail product_id="101" quantity="120"/>
<orderdetail product_id="202" quantity="100"/>
</order>
</customer>
</root>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
INSERT INTO KHAN
SELECT customer_id,
contact_name, 
order_date,
order_id, 
product_id,
quantity
FROM OPENXML (@idoc, '/root/customer',3)
      WITH (customer_id  varchar(50),
            contact_name Varchar(50), 
            order_date	 DATETIME,
            order_id     Varchar(50), 
            product_id   Varchar(50),
            quantity	 DECIMAL(18,4)')
EXEC sp_xml_removedocument @idoc

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-09 : 20:46:09

I think it should be like this:

quote:

Originally posted by visakh16

this will be sql 2000 equivalent


DECLARE @idoc int
DECLARE @doc varchar(5000)
SET @doc ='
<root>
<customer customer_id="JH01" contact_name="John harriot">
<order order_id="1001" customer_id="JH01" order_date="2006-07-04">
<orderdetail product_id="11" quantity="12"/>
<orderdetail product_id="22" quantity="10"/>
</order>
</customer>
<customer customer_id="GH02" contact_name="khan harriot">
<order order_id="1002" customer_id="GH02" order_date="2006-07-05">
<orderdetail product_id="101" quantity="120"/>
<orderdetail product_id="202" quantity="100"/>
</order>
</customer>
</root>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
INSERT INTO KHAN
SELECT  A.customer_id,
B.contact_name,
A.order_date,
A.order_id, 
A.product_id,
A.quantity 
from 
(select * 
FROM OPENXML (@idoc, '/root/customer/order/orderdetail',3)
      WITH (customer_id  varchar(50) '../@customer_id',
            order_date	 DATETIME '../@order_date',
            order_id     Varchar(50) '../@order_id', 
            product_id   Varchar(50)'@product_id',
            quantity	 DECIMAL(18,4)'@quantity'))as A
inner join 
(Select * from 
OPENXML (@idoc, '/root/customer',3)
       WITH (customer_id  varchar(20),
             contact_name varchar(30))) as B
on A.customer_id = B.customer_id
EXEC sp_xml_removedocument @idoc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-10 : 02:40:17

what the purpose of inner join?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-10 : 09:53:41

quote:
Originally posted by visakh16

what the purpose of inner join?

To grab values from contact_name.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-11 : 17:35:33

I ran this SQL 2005 and it is working fine but not in SQL 2000.
I got error:
Failed to load Msxml2.dll.

This was bug and solution is:
http://support.microsoft.com/kb/279434

quote:

Originally posted by sodeep

I think it should be like this:

quote:

Originally posted by visakh16

this will be sql 2000 equivalent


DECLARE @idoc int
DECLARE @doc varchar(5000)
SET @doc ='
<root>
<customer customer_id="JH01" contact_name="John harriot">
<order order_id="1001" customer_id="JH01" order_date="2006-07-04">
<orderdetail product_id="11" quantity="12"/>
<orderdetail product_id="22" quantity="10"/>
</order>
</customer>
<customer customer_id="GH02" contact_name="khan harriot">
<order order_id="1002" customer_id="GH02" order_date="2006-07-05">
<orderdetail product_id="101" quantity="120"/>
<orderdetail product_id="202" quantity="100"/>
</order>
</customer>
</root>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
INSERT INTO KHAN
SELECT  A.customer_id,
B.contact_name,
A.order_date,
A.order_id, 
A.product_id,
A.quantity 
from 
(select * 
FROM OPENXML (@idoc, '/root/customer/order/orderdetail',3)
      WITH (customer_id  varchar(50) '../@customer_id',
            order_date	 DATETIME '../@order_date',
            order_id     Varchar(50) '../@order_id', 
            product_id   Varchar(50)'@product_id',
            quantity	 DECIMAL(18,4)'@quantity'))as A
inner join 
(Select * from 
OPENXML (@idoc, '/root/customer',3)
       WITH (customer_id  varchar(20),
             contact_name varchar(30))) as B
on A.customer_id = B.customer_id
EXEC sp_xml_removedocument @idoc

suman.reddy39
Starting Member

34 Posts

Posted - 2009-01-12 : 00:00:28

thank you all for this solution.

Don't go the way Life takes you, Take the Life the way you go

subbi

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-12 : 00:05:06

You are Welcome!!!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-15 : 16:09:54

No need of Inner Join.


DECLARE @idoc int
DECLARE @doc varchar(5000)
SET @doc ='
<root>
<customer customer_id="JH01" contact_name="John harriot">
<order order_id="1001" customer_id="JH01" order_date="2006-07-04">
<orderdetail product_id="11" quantity="12"/>
<orderdetail product_id="22" quantity="10"/>
</order>
</customer>
<customer customer_id="GH02" contact_name="khan harriot">
<order order_id="1002" customer_id="GH02" order_date="2006-07-05">
<orderdetail product_id="101" quantity="120"/>
<orderdetail product_id="202" quantity="100"/>
</order>
</customer>
</root>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc


Select * 
FROM OPENXML (@idoc, '/root/customer/order/orderdetail',3)
      WITH (customer_id  varchar(50) '../@customer_id',
            order_date	 DATETIME '../@order_date',
            Contact_name Varchar(50)'../../@contact_name',
            order_id     Varchar(50) '../@order_id', 
            product_id   Varchar(50)'@product_id',
            quantity	 DECIMAL(18,4)'@quantity')

EXEC sp_xml_removedocument @idoc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources