OPENXML Stored Procedure

I've already posted this question once but I'm posting it again, re-written to be more clear than the previous post as I have not received any replies...
We will be receiving several XML documents of various types. We need to shred these documents and get the data into SQL. That's fine, I can and have done that but each time it means I have to create very specific insert query for a particular document typ
e. Our system now must be extended to become more generic so that we can add new document types instantly (or pretty close to it). So, I've created a very generic database structure that includes three tables. One table holds attributes (table column name
s), associated XML node name, data type and the foreign key relating to the document types table. The last table holds the value of the node and the attribute id.
The problem I'm having is with building a dynamic OPENXML statement.
Here's the code (abridged):
INSERT case_oce
SELECT * FROM OPENXML(@.xmlDoc,'/official_copy',2)
WITH (
(SELECT attribute_name, attribute_type, document_node
FROM v_xml_case_documents WHERE)
)
Here is a sample of the results I'm trying to achieve, however, dynamically through the select statement above:
INSERT case_oce
SELECT * FROM OPENXML(@.xmlDoc,'/official_copy',2)
WITH (
co_property_lender nvarchar(255) '/official_copy/title_number',
co_property_price decimal '/official_copy/property_register/price',
co_property_legal_description nvarchar(4000) '/official_copy/property_register/legal_description',
co_property_address_house_name nvarchar(50) '/official_copy/property_address/house_name',
co_property_address_house_number nvarchar(50) '/official_copy/property_address/house_number',
co_property_address_street_name nvarchar(50) '/official_copy/property_address/street_name'
)
Do you know of a better way or any way to do this? Is this even possible with OPENXML? Does anyone have any ideas/comments/examples?
TIA
Denise White
You have to build up a string in T-SQL that you then execute using EXECUTE.
The WITH clause is a constant expression since we need to know the SQL
rowset format at compiletime.
Best regards
Michael
"mizwhite" <anonymous@.discussions.microsoft.com> wrote in message
news:1C7757ED-0787-48AA-9EDE-FF7BD36F7DAE@.microsoft.com...
> I've already posted this question once but I'm posting it again,
> re-written to be more clear than the previous post as I have not received
> any replies...
> We will be receiving several XML documents of various types. We need to
> shred these documents and get the data into SQL. That's fine, I can and
> have done that but each time it means I have to create very specific
> insert query for a particular document type. Our system now must be
> extended to become more generic so that we can add new document types
> instantly (or pretty close to it). So, I've created a very generic
> database structure that includes three tables. One table holds attributes
> (table column names), associated XML node name, data type and the foreign
> key relating to the document types table. The last table holds the value
> of the node and the attribute id.
> The problem I'm having is with building a dynamic OPENXML statement.
> Here's the code (abridged):
> INSERT case_oce
> SELECT * FROM OPENXML(@.xmlDoc,'/official_copy',2)
> WITH (
> (SELECT attribute_name, attribute_type, document_node
> FROM v_xml_case_documents WHERE)
> )
>
> Here is a sample of the results I'm trying to achieve, however,
> dynamically through the select statement above:
> INSERT case_oce
> SELECT * FROM OPENXML(@.xmlDoc,'/official_copy',2)
> WITH (
> co_property_lender nvarchar(255) '/official_copy/title_number',
> co_property_price decimal '/official_copy/property_register/price',
> co_property_legal_description nvarchar(4000)
> '/official_copy/property_register/legal_description',
> co_property_address_house_name nvarchar(50)
> '/official_copy/property_address/house_name',
> co_property_address_house_number nvarchar(50)
> '/official_copy/property_address/house_number',
> co_property_address_street_name nvarchar(50)
> '/official_copy/property_address/street_name'
> )
>
> Do you know of a better way or any way to do this? Is this even possible
> with OPENXML? Does anyone have any ideas/comments/examples?
> TIA
> Denise White
>
|||Thanks for your reply Michael. I am still, however, in need of an answer to my original problem. I need to build the field list dynamically (i.e., co_property_lender nvarchar(255) '/official_copy/title_number', etc.) as I have stored this data in the data
base for each document type. I obviously can't set a query result set to a variable, thus my problem remains.
Thanks for your help.
Denise.
|||I've been working further on this problem. As suggested, I've built up a T-SQL string and used EXECUTE. It works but not when I try to obtain my field list dynamically.
--Source XML Document
DECLARE @.doc nvarchar (4000)
SET @.doc ='<official_copy><property_register><property_addr ess><house_name>Little Brae</house_name><house_number>50</house_number><street_name>98 North Brink</street_name><district_name /><post_town>Wisbech</post_town><county>Cambridgeshire</county><postc
ode1>OL2</postcode1><postcode2>5BY</postcode2></property_address></property_register></official_copy>'
--Load XML document
DECLARE @.idoc int
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
--Dynamically generated SQL statement
DECLARE @.sqlStatement nvarchar(4000)
--Our main params that we would like to pass/control
DECLARE @.primaryXPath nvarchar(255)
DECLARE @.selectFieldList varchar(2000)
--First Test
SET @.primaryXPath = '/official_copy'
/* this works --
SET @.selectFieldList = '[house_name] NVARCHAR(100) ''/official_copy/property_register/property_address/house_name'',
[house_number] NVARCHAR(100) ''/official_copy/property_register/property_address/house_number'''
*/
SET @.selectFieldList = 'SELECT attribute_name, attribute_type, document_node
FROM v_xml_case_documents WHERE cd_type_id = 1'
SET @.sqlStatement = 'SELECT * FROM OPENXML (@.idoc, @.primaryXPath, 2) WITH (' + @.selectFieldList + ')'
EXECUTE sp_executesql @.sqlStatement,
@.params = N'@.idoc INT, @.primaryXPath nvarchar(255), @.selectFieldList varchar(8000)',
@.idoc=@.idoc, @.primaryXPath=@.primaryXPath, @.selectFieldList=@.selectFieldList
|||If you multiple rows in the table that defines your @.selectFieldList variable then you should open a cursor and append the values you need to it...
_Randal
|||Thanks Randal. I have made use of your suggestion to use a cursor and I've incorporated that with the code I already had for building a dynamic SQL query.
I'm getting closer to a working solution.The problem now is that this code is coming back with the following error:
"Syntax error converting the nvarchar value 'INSERT INTO case_documents(cd_attribute) SELECT * FROM OPENXML(' to a column of data type int." -- Obviously, the error message is pretty straightforward but I cannot understand why it would return this as an
error. I have tried referencing the @.idoc handle both with and without the ' ' characters. Still no joy. Is anyone else having similar problems with building a dynamic field list in OPENXML? - - Any additional suggestions? Examples? ;)
Here is my revised code:
DECLARE @.caseID int
DECLARE @.CaseTypeID int
DECLARE @.selectFieldList nvarchar(4000)
DECLARE @.sqlStatement nvarchar(4000)
DECLARE @.fieldName nvarchar(4000)
DECLARE @.xmlRoot nvarchar(255)
SET @.caseID = 123456
SET @.CaseTypeID = 1
SET @.xmlRoot = '/official_copy'
-- Create a test table. This table schema is used by OPENXML as the
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
DECLARE @.primaryXPath nvarchar(255)
-- XML Input
SET @.doc ='
<official_copy><property_register><property_addres s><house_name>Little Brae</house_name><street_name>98 North Brink</street_name><correspondence><something>Little Brae</something></correspondence></property_address></property_register></official_copy>
'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
-- BEGIN LOOP
DECLARE @.retcode int
DECLARE importCase CURSOR
FOR (SELECT document_node FROM case_document_attribute WHERE cd_type_id = @.CaseTypeID)
OPEN importCase
FETCH NEXT FROM importCase INTO @.primaryXPath
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
SET @.selectFieldList = @.primaryXPath
SET @.sqlStatement = 'INSERT INTO case_documents(cd_attribute) '
SET @.sqlStatement = @.sqlStatement + 'SELECT * FROM OPENXML('
SET @.sqlStatement = @.sqlStatement + @.iDoc
SET @.sqlStatement = @.sqlStatement + ','
SET @.sqlStatement = @.sqlStatement + ''''+ @.xmlRoot + ''''
SET @.sqlStatement = @.sqlStatement + ',2)'
SET @.sqlStatement = @.sqlStatement + ' WITH ('
SET @.sqlStatement = @.sqlStatement + 'cd_attribute text ' + '''' + @.primaryXPath + '''' + ')'
/*SET @.sqlStatement = 'INSERT INTO case_documents '
SET @.sqlStatement = @.sqlStatement + 'SELECT * FROM OpenXml(' + @.iDoc + ','
SET @.sqlStatement = @.sqlStatement + '''' + @.primaryXPath + ''''
SET @.sqlStatement = @.sqlStatement + ',2)'
SET @.sqlStatement = @.sqlStatement + 'WITH (' + '''' + @.primaryXPath + '''' + ')'*/
EXEC sp_executesql @.sqlStatement
--PRINT @.sqlStatement
END
FETCH NEXT FROM importCase INTO @.primaryXPath
END
CLOSE importCase
DEALLOCATE importCase
-- unload doc
EXEC sp_xml_removedocument @.idoc
***
Many many thanks.
Denise.
|||This is not really an OpenXML issue anymore. This error message just says
that you cannot concatenate an integer to a string type since the string
value does not become an int.
Try:
SET @.sqlStatement = @.sqlStatement + Cast(@.iDoc as nvarchar(10))
Best regards
Michael
"mizwhite" <anonymous@.discussions.microsoft.com> wrote in message
news:9B4FD306-4311-41CC-86E5-EB3F0670BDF6@.microsoft.com...
> Thanks Randal. I have made use of your suggestion to use a cursor and I've
> incorporated that with the code I already had for building a dynamic SQL
> query.
> I'm getting closer to a working solution.The problem now is that this code
> is coming back with the following error:
> "Syntax error converting the nvarchar value 'INSERT INTO
> case_documents(cd_attribute) SELECT * FROM OPENXML(' to a column of data
> type int." -- Obviously, the error message is pretty straightforward but
> I cannot understand why it would return this as an error. I have tried
> referencing the @.idoc handle both with and without the ' ' characters.
> Still no joy. Is anyone else having similar problems with building a
> dynamic field list in OPENXML? - - Any additional suggestions? Examples?
> ;)
> Here is my revised code:
> DECLARE @.caseID int
> DECLARE @.CaseTypeID int
> DECLARE @.selectFieldList nvarchar(4000)
> DECLARE @.sqlStatement nvarchar(4000)
> DECLARE @.fieldName nvarchar(4000)
> DECLARE @.xmlRoot nvarchar(255)
> SET @.caseID = 123456
> SET @.CaseTypeID = 1
> SET @.xmlRoot = '/official_copy'
> -- Create a test table. This table schema is used by OPENXML as the
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> DECLARE @.primaryXPath nvarchar(255)
> -- XML Input
> SET @.doc ='
> <official_copy><property_register><property_addres s><house_name>Little
> Brae</house_name><street_name>98 North
> Brink</street_name><correspondence><something>Little
> Brae</something></correspondence></property_address></property_register></official_copy>
> '
> -- Create an internal representation of the XML document.
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> -- BEGIN LOOP
> DECLARE @.retcode int
> DECLARE importCase CURSOR
> FOR (SELECT document_node FROM case_document_attribute WHERE cd_type_id =
> @.CaseTypeID)
> OPEN importCase
> FETCH NEXT FROM importCase INTO @.primaryXPath
> WHILE (@.@.FETCH_STATUS <> -1)
> BEGIN
> IF (@.@.FETCH_STATUS <> -2)
> BEGIN
> SET @.selectFieldList = @.primaryXPath
> SET @.sqlStatement = 'INSERT INTO case_documents(cd_attribute) '
> SET @.sqlStatement = @.sqlStatement + 'SELECT * FROM OPENXML('
> SET @.sqlStatement = @.sqlStatement + @.iDoc
> SET @.sqlStatement = @.sqlStatement + ','
> SET @.sqlStatement = @.sqlStatement + ''''+ @.xmlRoot + ''''
> SET @.sqlStatement = @.sqlStatement + ',2)'
> SET @.sqlStatement = @.sqlStatement + ' WITH ('
> SET @.sqlStatement = @.sqlStatement + 'cd_attribute text ' + '''' +
> @.primaryXPath + '''' + ')'
>
> /*SET @.sqlStatement = 'INSERT INTO case_documents '
> SET @.sqlStatement = @.sqlStatement + 'SELECT * FROM OpenXml(' + @.iDoc + ','
> SET @.sqlStatement = @.sqlStatement + '''' + @.primaryXPath + ''''
> SET @.sqlStatement = @.sqlStatement + ',2)'
> SET @.sqlStatement = @.sqlStatement + 'WITH (' + '''' + @.primaryXPath +
> '''' + ')'*/
>
> EXEC sp_executesql @.sqlStatement
> --PRINT @.sqlStatement
> END
> FETCH NEXT FROM importCase INTO @.primaryXPath
> END
> CLOSE importCase
> DEALLOCATE importCase
> -- unload doc
> EXEC sp_xml_removedocument @.idoc
>
> ***
> Many many thanks.
> Denise.
|||"mizwhite" <anonymous@.discussions.microsoft.com> wrote in message
news:1C7757ED-0787-48AA-9EDE-FF7BD36F7DAE@.microsoft.com...
> I've already posted this question once but I'm posting it again,
re-written to be more clear than the previous post as I have not received
any replies...
> We will be receiving several XML documents of various types. We need to
shred these documents and get the data into SQL. That's fine, I can and have
done that but each time it means I have to create very specific insert query
for a particular document type. Our system now must be extended to become
more generic so that we can add new document types instantly (or pretty
close to it).
Hi!
If I understand you correctly, you have data coming in several XML files of
different formats, but with the same contents. You want these contents put
into a table.
If so, have you considered using XSLT to transform the different XML files
to a common format? Then you could leave your stored procedure alone and
still be very generic.
Just a thought...
- Kristoffer -
|||Kristoffer,
Yes, we have XML data coming in different formats but also with differing contents. The data does need to be extracted and placed in a database. What we've devised, is a generic database structure that assumes that each document type (i.e., home remortgag
e, home sale, transfer of equity, etc.) has an XML schema associated with it. We have a mapping table that basically stores the schemas for each document type (table column name, XPath node path and document type ID).
When we receive a document, a stored procedure will execute, a document type parameter is passed so we know what schema to query from the database and what I am trying to do now is output the XML fieldlist dynamically from that query. This will give us a
generic system that is able to accomodate many types of documents with little programming effort (aside from the schema and adding the schema to the mapping table in the DB).
Any thoughts?
Here's the latest code incase anyone is interested:
DECLARE @.caseID int
DECLARE @.CaseTypeID int
DECLARE @.selectFieldList nvarchar(4000)
DECLARE @.sqlStatement nvarchar(4000)
DECLARE @.fieldName nvarchar(4000)
DECLARE @.xmlRoot nvarchar(255)
SET @.caseID = 123456
SET @.CaseTypeID = 1
SET @.xmlRoot = '/official_copy'
-- Create a test table. This table schema is used by OPENXML as the
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
DECLARE @.primaryXPath nvarchar(255)
-- XML Input
SET @.doc ='
<official_copy><property_register><property_addres s><house_name>Little Brae</house_name><street_name>98 North Brink</street_name><correspondence><something>Little Brae</something></correspondence></property_address></property_register></official_copy>
'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
-- BEGIN LOOP
DECLARE @.retcode int
DECLARE importCase CURSOR
FOR (SELECT document_node FROM case_document_attribute WHERE cd_type_id = @.CaseTypeID)
OPEN importCase
FETCH NEXT FROM importCase INTO @.primaryXPath
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
SET @.selectFieldList = @.primaryXPath
SET @.sqlStatement = 'INSERT INTO case_documents(cd_attribute) '
SET @.sqlStatement = @.sqlStatement + 'SELECT * FROM OPENXML('
SET @.sqlStatement = @.sqlStatement + Cast(@.iDoc as nvarchar(100))
SET @.sqlStatement = @.sqlStatement + ','
SET @.sqlStatement = @.sqlStatement + ''''+ @.xmlRoot + ''''
SET @.sqlStatement = @.sqlStatement + ',2)'
SET @.sqlStatement = @.sqlStatement + ' WITH ('
SET @.sqlStatement = @.sqlStatement + 'cd_attribute nvarchar(255) ' + '''' + @.primaryXPath + '''' + ')'
/*SET @.sqlStatement = 'INSERT INTO case_documents '
SET @.sqlStatement = @.sqlStatement + 'SELECT * FROM OpenXml(' + @.iDoc + ','
SET @.sqlStatement = @.sqlStatement + '''' + @.primaryXPath + ''''
SET @.sqlStatement = @.sqlStatement + ',2)'
SET @.sqlStatement = @.sqlStatement + 'WITH (' + '''' + @.primaryXPath + '''' + ')'*/
EXEC sp_executesql @.sqlStatement
PRINT @.sqlStatement
END
FETCH NEXT FROM importCase INTO @.primaryXPath
END
CLOSE importCase
DEALLOCATE importCase
EXEC sp_xml_removedocument @.idoc
TIA,
Denise