November 5, 2009 at 2:59 pm
Hi,
I have an XML file that has image as well as Video information there . I need to import these data into two tables one for the images and the other for Video in the database separately . I need to use a Stored proc for this .
Any help appreciated .
Thanks,
November 5, 2009 at 3:16 pm
Hi PSB
If your XML is not too large, you can load the file into a XML variable and use XPath to get the data (into your tables).
<?xml version="1.0" encoding="UTF-8"?>
<Root>
<ProductDescription ProductModelID="5">
<Summary>Some Text</Summary>
</ProductDescription>
</Root>
DECLARE @xml XML;
SELECT @xml = (
SELECT * FROM OPENROWSET(
BULK 'C:\Users\Flo\Temp\Test\test.xml',
SINGLE_BLOB) x)
SELECT
T.C.value('(Summary/text())[1]', 'nvarchar(100)')
FROM @xml.nodes('Root/ProductDescription') T(C)
Greets
Flo
November 10, 2009 at 9:45 am
I am getting the following error after running the query below :
Cannot bulk load. The file "C:\test.xml" does not exist.
DECLARE @xml XML;
SELECT @xml = (
SELECT * FROM OPENROWSET(
BULK 'C:\test.xml',
SINGLE_BLOB) x)
SELECT
T.C.value('(Summary/text())[1]', 'nvarchar(100)')
FROM @xml.nodes('Root/ProductDescription') T(C)
Thanks,
PSB
November 10, 2009 at 10:20 am
Most probably the file is different than the sample file Flo used in his example due to the lack of any further information... Change 'C:\test.xml' to the real file destination.
Also, I'm expecting an error for the second part of Flos sample as well since we don't know any structure of the xml you're using.
Please note that we cannot look over your should. So we don't see what you see.
In order to help us help you it's always helpful to have sample data to test against...
Please see the first link in my signature for further details.
November 10, 2009 at 10:38 am
Below is the query that I am using
INSERT INTO TestPB(firstname, lastname)
SELECT X.product.query('fname').value('.', 'VARCHAR(30)'),
X.product.query('lname').value('.', 'VARCHAR(30)')
FROM (
SELECT CAST(X AS XML)
FROM OPENROWSET(
BULK INSERT TestPB FROM 'C:\Documents and Settings\xyz\Desktop\test.xml',
SINGLE_BLOB) AS T(x)
) AS T(x)
CROSS APPLY x.nodes('TestPB/Customer') AS X(product);
and the xml is
<?xml version="1.0" encoding="UTF-8" ?>
- <Customers>
- <Customer>
<fname>fryan</fname>
<lname>valdez</lname>
</Customer>
- <Customer>
<fname>auric</fname>
<lname>valdez</lname>
</Customer>
- <Customer>
<fname>jayson</fname>
<lname>valdez</lname>
</Customer>
</Customers>
Now I get the following error "Incorrect syntax near the keyword 'INSERT'."
Thanks,
PSB
November 10, 2009 at 11:27 am
The problem is you're trying to do the insert twice:
1)
FROM OPENROWSET(
BULK INSERT TestPB FROM 'C:\Documents and Settings\xyz\Desktop\test.xml',
SINGLE_BLOB) AS T(x)
) AS T(x)
and
2)
INSERT INTO TestPB(firstname, lastname)
SELECT
You get the error because OPENROWSET does not support the BULK INSERT syntax. (see BooksOnLine for details).
AFAIK there is no way to directly use the OPENROWSET syntax as a direct source for XQuery. Therefore, I'd recommend to store the data in a variable and query the variable.
Based on your sample data it would look like:
DECLARE @TestPB TABLE (firstname VARCHAR(30), lastname VARCHAR(30))
DECLARE @xml XML;
SELECT @xml = (
SELECT * FROM OPENROWSET(
BULK 'C:\Documents and Settings\xyz\Desktop\test.xml',
SINGLE_BLOB) x)
INSERT INTO @TestPB(firstname, lastname)
SELECT X.product.value('fname[1]', 'VARCHAR(30)'),
X.product.value('lname[1]', 'VARCHAR(30)')
FROM
@xml.nodes('Customers/Customer') AS X(product);
SELECT * FROM @TestPB
/* result set
firstnamelastname
fryanvaldez
auricvaldez
jaysonvaldez
*/
November 10, 2009 at 11:34 am
I get the following error "Cannot bulk load because the file "C:\Documents and Settings\pbaruah\Desktop\test.xml" could not be opened. Operating system error code 3(The system cannot find the path specified.).
"
after excuting the query below
DECLARE @TestPB TABLE (firstname VARCHAR(30), lastname VARCHAR(30))
DECLARE @xml XML;
SELECT @xml = (
SELECT * FROM OPENROWSET(
BULK 'C:\Documents and Settings\xyz\Desktop\test.xml',
SINGLE_BLOB) x)
INSERT INTO @TestPB(firstname, lastname)
SELECT X.product.value('fname[1]', 'VARCHAR(30)'),
X.product.value('lname[1]', 'VARCHAR(30)')
FROM
@xml.nodes('Customers/Customer') AS X(product);
SELECT * FROM @TestPB
Thanks,
PSB
November 10, 2009 at 11:46 am
The problem is described pretty clear:
The system cannot find the path specified
It looks like you're trying to open a file located on a client PC from a (SQL) server.
Either you have the chance to dump the file into a folder located on the server where SQL Server is installed or you have any other way to access the file from the server.
If it's not an client/server issue it might be something more simple, e.g. a typo...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply