August 25, 2009 at 6:15 am
I do not understand why I am getting Nulls as a result set. can someone take a look at the following sample and give me some feed back.
Here is the Script.
/* *************** Here is the sccript ****************/
DECLARE @idoc int
DECLARE @doc xml
SET @doc =(select * from dbo.xmlimport for xml raw, elements)
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc, '
SELECT *--id, parentid, nodetype, localname, prefix, Prev, text
FROM OPENXML (@idoc, 'row/xmlStr//',1)
with (STATUSvarchar(20)'nsf:FINAXXML//@BATCHSTATUS',
TheName2varchar(20)'./nsf:DOCUMENTFIELD/@NAME'
)
exec sp_xml_Removedocument @idoc
/* *************** Here is the Data ****************/
all Help will be appreciated.
thanks
"We never plan to Fail, We just fail to plan":)
August 25, 2009 at 4:05 pm
What is your expected output?
Could you use XQuery instead of openxml?
If yes, here's a simple example on how to query the @id attribute as well as the status element.
If you have further questions please provide some information regarding the data you're looking for.
;WITH XMLNAMESPACES (
'http://www.W3C.com/dtd/' AS nsf)
SELECT
c.value ('nsf:BATCHCLASS[1]/@ID[1]','varchar(30)') AS ID,
c.value ('nsf:BATCHSTATUS[1]','varchar(30)') AS Status
FROM @doc.nodes ('nsf:XMLRELEASE/nsf:FINAXXML') AS T(c)
August 26, 2009 at 7:48 am
Sorry.
the goal is to import the file directly to SQL, once in SQL I will proceed to Shred the xml part into one table and then populate other tables.
thanks
if y'all know of something better (not using third party software) let me know.
"We never plan to Fail, We just fail to plan":)
August 26, 2009 at 2:14 pm
There's no reason to be sorry.
It's just a new information that you're trying to import from a file. 😉
Your initial post fills the xml variable using a select statement based on a simple table...
However, what you're trying to achieve is a little confusing to me:
First post:
SET @doc = (select * from dbo.xmlimport for xml raw, elements)
Second post:
the goal is to import the file directly to SQL, once in SQL I will proceed to Shred the xml part into one table
The question is: What are you trying to do? :ermm:
If you want to read the xml data from a file into a xml variable, you can use the following code: declare @doc xml
select @doc = convert(xml,BulkColumn)
from openrowset(bulk 'c:\yourfile.xml', single_blob) as f.
After this step you still can use the code as proposed in my previous post...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply