Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 463
  • Last Modified:

OpenXML soultuion clarification

I found a solution I believe to work provided by Hilaire, but I am unsure of how to get the results.   The solution I found is here: http://www.experts-exchange.com/Database/Miscellaneous/Q_21390833.html?sfQueryTermInfo=1+openxml .  I was able to create the stored procedure but when I run that last bit of code to work the magic I get 0 results.  I did change the path to a valid XML file located locally.

I am trying to read a very large XML file in SQL so that I may query it and put certain records in an existing database.
0
websuperman
Asked:
websuperman
  • 7
  • 5
1 Solution
 
BrandonGalderisiCommented:
Define "VERY LARGE" because he states "This version handles files up to 74000 characters".

That is also 3+ years old and probably written for SQL 2000 so why don't you say what you want to do, and then we can decide if that's right for you.
0
 
websupermanAuthor Commented:
Good Point.  I am using SQL 2005  The file is 63,000 KB.  I need to query it against other data sources so that I may extract data from this very large XML file and input it into the database.  It is icecat xml.  My ultimate goal is to get all the product data into a web site.  The information I really need for now is the path, product_ID, Prod_ID and Catid.  I woud like to have and be able to use the product information as well but that seems a bit lofty for me at the moment.  The structure of this file is as follows.  

<?xml version="1.0"?>
<!DOCTYPE ICECAT-interface SYSTEM "http://data.icecat.biz/dtd/files.index.dtd">
<ICECAT-interface>
	<files.index Generated="20081013071404">
		<file path="export/freexml/10.xml" Product_ID="10" Updated="20081007111957" Quality="ICECAT" Supplier_id="1" Prod_ID="C8934A#A2L" Catid="234" On_Market="0"/>
		<file path="export/freexml/1286.xml" Product_ID="1286" Updated="20081007111957" Quality="SUPPLIER" Supplier_id="1" Prod_ID="D9190B" Catid="156" On_Market="0"/>
		<file path="export/freexml/1287.xml" Product_ID="1287" Updated="20081007111957" Quality="SUPPLIER" Supplier_id="1" Prod_ID="D9192B" Catid="156" On_Market="0"/>
		<file path="export/freexml/1288.xml" Product_ID="1288" Updated="20081007111957" Quality="SUPPLIER" Supplier_id="1" Prod_ID="D9194B" Catid="156" On_Market="0"/>
		<file path="export/freexml/1289.xml" Product_ID="1289" Updated="20081007111957" Quality="SUPPLIER" Supplier_id="1" Prod_ID="D9196B" Catid="156" On_Market="0"/>
	</files.index>
</ICECAT-interface>

Open in new window

0
 
BrandonGalderisiCommented:
I can assure you that 63mb is more than 74k characters :)

Due to it's size, you may be better off opening and parsing the xml file in a different language and inserting records into SQL.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
websupermanAuthor Commented:
hmmm, not sure where to go from here then.  If I could get the data into SQL I can handle that, trick right now seems to be getting the data accessible for queries.  Any suggestions?
0
 
BrandonGalderisiCommented:
create table #XML (MyXML XML null)

insert into #XML(MyXML)
   SELECT * FROM OPENROWSET(BULK N'C:\Text1.xml', SINGLE_BLOB) AS Document

--Do stuff with the XML

drop table #xml
GO
0
 
websupermanAuthor Commented:
Tried this without success, probably not following:

create table #XML (MyXML XML null)

insert into #XML(MyXML)
   SELECT * FROM OPENROWSET(BULK N'C:\Text.xml', SINGLE_BLOB) AS Document

Update MyTable SET MyTable.category = #XML.CatID Where MyTable.sku = #XML.Prod_ID

drop table #xml
GO

No Good, :
The multi-part identifier "#XML.Prod_ID" could not be bound.
I tried multiple variations to get away from the error without luck, testing on smaller file.
0
 
BrandonGalderisiCommented:
I'm working something else up.  But I need to know if you need to filter by a particular <files.index Generated> or if there will always be only one files.index node.

quickly perferably
0
 
BrandonGalderisiCommented:
Here you go:
declare @XML XML 
     ,@i int
 
select @XML=convert(xml, bulkcolumn,2) from openrowset(BULK N'c:\temp\test.xml',SINGLE_BLOB) as Document
 
exec sp_xml_preparedocument @i=@i output, @xml=@xml
 
select * from openxml (@i,'/ICECAT-interface/files.index/file')
     with ([path]        nvarchar(max)
          ,Product_ID    int
          ,Prod_ID       nvarchar(max)
          ,Catid         int)
 
exec sp_xml_removedocument @i=@i

Open in new window

0
 
websupermanAuthor Commented:
should always be a single <files.index> node
0
 
BrandonGalderisiCommented:
Then that (http:#22726098)should work.
0
 
BrandonGalderisiCommented:
then that (http:#22726098 ) should work.
0
 
websupermanAuthor Commented:
Thank you, this works great and meets my needs.  I appreciate the speedy responses.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now