I've been banging my head against this one for a while. There are examples around but I can't seem to find anything that works (or find an explanation).
The problem: The xml document uses an imported common schema to reference common tags/objects (xmlns:cmn) but I can't seem to get openXML working with multiple namespaces. If I remove the primary namespace (emboldened in red) the SELECT returns as expected. Editing the xml document is not an option.
I have an XML document that I am trying to use within a data flow (XML Source)--the problem is that I keep getting an error message stating that the XML document has multiple namespaces and therefore, SSIS will not create the XSD for me. If I take out the second namespace, I am able to successfully get the task to execute, but this XML is created with 2 namespaces and there is no way to get around this (I cannot get the report server to change these parameters)--my question is: know a way to get SSIS to handle multiple namespaces so that I can process this XML document and extract the necessary data elements from it.
I'm having trouble getting the namespaces correct in the XML that is generated by SQL. The XML consists of two parts: a header part with some context information and the payload part with the actual data. With my current SQL script, the namespace is also copied to the payload part.
Here are some DDL and DML scripts to generate two test tables and some test data:
Code: CREATE TABLE dbo.context( idintNOT NULLconstraint pk_dbo_context primary key, namenvarchar(100)NOT NULL ) INSERT INTO dbo.context(id, name) VALUES (1, 'Here comes some great context information.')
[Code] ....
This is my current FOR XML script that generates the XML:
Code: ;WITH XMLNAMESPACES (DEFAULT 'http://services.registersubscription-02_00.a.cool.url.com') SELECT CEXT.name AS [Context/Name], (SELECT CONT.id AS [Content/Reference],
[Code] ....
This generates this XML (notice that the namespace is repetated in the <Questions> element):
Code: <RegisterSubscription xmlns="http://services.registersubscription-02_00.a.cool.url.com"> <Request> <Context> <Name>Here comes some great context information.</Name>
[Code] ....
The XML should be like this:
Code: <RegisterSubscription xmlns="http://services.registersubscription-02_00.a.cool.url.com"> <Request xmlns=""> <Context> <Name>Here comes some great context information.</Name>
[Code] ....
The generated XML does not pass the XSD validation.
Hi eveyone, I'm reading about namespaces and schemas in MS SQL 2005, and that's exactly what I was looking for for the structure of my database.
I've a database and 4 different applications that will use it, and every application will create a new database every year, for example, i'd like to use the following schema:
MainDatabase.MyApplication.MyYear.MyTable
In MS SQL i've created my main schema using:
USE Mydatabase; CREATE SCHEMA MyApplication GO
Now I want to add sub-groups (in this example, I need to create Year2006, Year2007, and so on).
Even If i create those schemas, how can I "add" them inside the Salary main schema? I've tryed with ALTER SCHEMA MyApplication TRANSFER Year2006
but it doesn't work since Year2006 is not an object.
I'm trying use the SSIS XML Task to process some XML files that have namespaces and schemas defined . One of the things I am doing is trying to perform an XPATH query on the files. However, I'm unable to provide either a namespace manager or XsltContext to the XML Task and that causes the XML Task to not understand my XPATH queries.
For example, if my XML is:
<o:object id="12345" xmlns:o="urn:some-urn"/>
and my desired XPATH is "/o:object/@id", I get the following error:
Error: 0xC002F304 at Query, XML Task: An error occurred with the following error message: "Namespace Manager or XsltContext needed. This query has a prefix, variable, or user-defined function.".
Of course, the XPATH "/object/@id" returns nothing as expected.
I am trying to select data from a column that has xml data in it. I have tried all I can think of, but the results keep coming back as NULL. Not sure why but I pasted the top of the xml document so show what I am trying to do.
WITH XMLNAMESPACES('[ns0]' AS ns) SELECT sourceclaimxml.value('(/ST01_TransactionSetIdentifierCode)[2]', 'varchar(300)') XMLValue FROM xclaim_audit_xml CROSS APPLY sourceclaimxml.nodes('//ST') as P(nref) WHERE edixid='2323111'
My problem is this: There can be a variable number of line elements (between 2 and 5), and in addition each element tag is identical to the others, i.e. Line.
I am trying to populate a SQL Server 2000 table (which contains the columns AddressLine1, AddressLine2, etc.) using OpenXML
Hi,My application writes data into sql server.Currently it converts data into XML (an in memory XML string) and writeusing OPENXML.I want to know if i write it to a csv file and use BCP, then will it befaster then OPENXML. (i feel, writing to a csv will create IO operation thatwill slow down the process).Thanks
I have a SP set up to take an input param and then fire the followingsp_xml_preparedocumentOPENXML (With a select statement)sp_xml_removedocumentThis works fine with the XML syntax that microsoft provides in itsdocumentation ...<ROOT><Customer><CustomerID>VINET</CustomerID><ContactName>Paul Henriot</ContactName><Order OrderID="10248" CustomerID="VINET" EmployeeID="5"OrderDate="1996-07-04T00:00:00"><OrderDetail ProductID="11" Quantity="12"/><OrderDetail ProductID="42" Quantity="10"/></Order></Customer><Customer><CustomerID>LILAS</CustomerID><ContactName>Carlos Gonzlez</ContactName><Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"OrderDate="1996-08-16T00:00:00"><OrderDetail ProductID="72" Quantity="3"/></Order></Customer></ROOT>The problem I am having is parsing XML data given to me in this format<?xml version="1.0" standalone="yes"?><Active_x0020_Directory_x0020_Users><Groups_Duz_x0060_mp><whenCreated>2/13/2004 7:13:21 PM</whenCreated><whenChanged>2/13/2004 7:13:21 PM</whenChanged><sAMAccountname>!WGSyEnBuCoBuDoCG</sAMAccountname><cn>!WGSyEnBuCoBuDoCG</cn><groupType>-2147483646</groupType></Groups_Duz_x0060_mp><Groups_Duz_x0060_mp><whenCreated>2/13/2004 7:12:04 PM</whenCreated><whenChanged>2/13/2004 7:12:04 PM</whenChanged><sAMAccountname>#11SeanTest</sAMAccountname><cn>#11SeanTest</cn><groupType>8</groupType></Groups_Duz_x0060_mp></Active_x0020_Directory_x0020_Users>As you can see the elements are defined differently, I have an XSLfile as follows<?xml version="1.0" standalone="yes"?><xs:schema id="Active_x0020_Directory_x0020_Users" xmlns=""xmlns:xs="http://www.w3.org/2001/XMLSchema"xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"><xs:element name="Active_x0020_Directory_x0020_Users"msdata:IsDataSet="true"><xs:complexType><xs:choice maxOccurs="unbounded"><xs:element name="Groups_Duz_x0060_mp"><xs:complexType><xs:sequence><xs:element name="whenCreated" type="xs:string"minOccurs="0" /><xs:element name="whenChanged" type="xs:string"minOccurs="0" /><xs:element name="sAMAccountname" type="xs:string"minOccurs="0" /><xs:element name="cn" type="xs:string" minOccurs="0" /><xs:element name="groupType" type="xs:string"minOccurs="0" /></xs:sequence></xs:complexType></xs:element></xs:choice></xs:complexType></xs:element></xs:schema>So now I am wondering how I tell SQL Server to use the new file formatfor XML?Thanks in advance for your help.
Whats wrong with the following code,its compiling but returning only first memberid. i want all memberids to be returned DECLARE @memberList VARCHAR(4000),@hDoc INT SET @memberList = '<MemberList><MemberID>7136</MemberID><MemberID>7137</MemberID><MemberID>7138</MemberID></MemberList>'exec sp_xml_preparedocument @hDoc OUTPUT, @memberListSELECT MemberID FROM OPENXML (@hdoc, 'MemberList', 2) WITH (MemberID BIGINT) EXEC sp_xml_removedocument @hDoc
Hello everyone. I am new to.Net and here is what I have to do. I needto update a SQL table with data coming from a XML file. I have seen some Microsoft documentation on this (the nice SQL statement that updates and inserts in the same stored procedure) but I don'tknow what is the best approach for passing my XML file to the stored procedure. The XML contains about 12 000 records, kind of phonebook info (name, email, phone). What would be the best approach to do this? What objects should I use? Thanks a million, Ben
Im trying to update a row that all collumns are required (can not be null). Let say if i want to remove "No2" from the insert command but by default i want to make sure that in my collumn "No2" in the table of my database i have zero into it.
================= with tbl_xmltest ================= My problem is on this line above. When i remove in my XML column No2 i got an error.. "'OpenXML'. This column cannot be NUL". Is there a way to use my sql-command
================================ declare @data nvarchar(4000) declare @handle int
--select top 1 * from tbl_xmltest for xml auto set @data = '<tbl_x005F_xmltest No1="1111" No2="234" No3="3333"/>'
exec sp_xml_preparedocument @handle output, @data
begin tran select * from tbl_xmltest
insert into tbl_xmltest select * from openxml(@handle, '//tbl_x005F_xmltest') with tbl_xmltest
select * from tbl_xmltest rollback EXEC sp_xml_removedocument @handle
Hello:I am leaning to use OPENXML in my queries, but I keep encountering problems:This is the code:========================================================DECLARE @xml_text VARCHAR(200)DECLARE @i INT SET @xml_text = '<ROOT><CODE>HAMHE</CODE><CODE>MENHE</CODE><CODE>REGCA</CODE><CODE>SLAGI</CODE></ROOT>' EXEC sp_xml_preparedocument @i OUTPUT, @xml_text SELECT * FROM OPENXML(@i, '/ROOT/CODE', 2) WITH CODE EXEC sp_xml_removedocument @i====================================================all I want is to return a return a result like this:HAMHEMENHEREGCASLAGIbut it keeps giving me error: "Server: Msg 208, Level 16, State 98, Line 129Invalid object name 'CODE'."Could anybody help me pls? thank you
Hello, I am using OpenXML to do inserts/updates on Sql Server. I have a problem with data type, for example, check this example: DECLARE @record VARCHAR(1000) SELECT @record = '<tests> <test> <NumberofChildren></NumberofChildren> <Name></Name> <Active></Active> </test></tests>' DECLARE @IndexInXML INT -- Create an internal representation of the XML document EXEC sp_xml_preparedocument @IndexInXML OUTPUT, @record INSERT INTO [test] SELECT [NumberofChildren], [Name], ISNULL([Active],null) FROM OPENXML (@IndexInXML, '/tests/test',2) WITH ( [NumberofChildren] INT, [Name] VARCHAR(50), [Active] BIT ) -- Remove in-memory table from memory EXEC sp_xml_removedocument @IndexInXML I have a table called test, it has the followig fields:1- ID2- NumberofChildren3- Name4- ActiveAll fields but ID are asisgned "Allow Null"I want to add data to this table, but if the value coming from xml is empty, I want to have null, in NumberofChildren, Name, Active.When I try my script, if the data is null, NumberofChildren with 0 value, Name is nothing, Active is 0,How can I make the INT and BIT datatypes have NULL when the input is empty in the xml? Can you helpthank you
When I use 'OPENXML' in SQL there is an error message appear, that is: 'XML parsing error: below tag has not been closed: NewDataSet.', is it related to my using LOOP method? how can I solve this problem? Here my SQL query for your reference: ''select @myn=UserName FROM OPENXML (@idoc, '/NewDataSet/Table',2) WITH (UserName varchar(20))"
I have an text column in a table that was intended to store XML data about queries that users submitted in our database. The problem appears that the application inserting records into the table doesn't do a great job of formatting the XML and I am having a great deal of trouble getting workable results.
The XML data in the table looks like: <request> <name>LastName</name> <oper>=</oper> <value>Smith</value> <name>FirstName</name> <oper>=</oper> <value>John</value> <name>MiddleName</name> <oper>=</oper> <value>Q.</value> </request>
Note the groups of three elements (name, oper, value). These always appear sequentially in the data and there are always a fixed number of elements. If an element has no data, then it contains the value 'null' (ie, <value>null</value>).
I want to get a result like:
Col1 Col2 Col3 ======== ====== ======== LastName = Smith FirstName = John MiddleName = Q
When I execute OPENXML, I specify option 2 (element centric) and the following WITH clause:
WITH (name varchar(10), oper varchar(10), value varchar(50) )
I get a single row returned. I have also tried:
WITH (name varchar(10) '@name', oper varchar(10) '@oper', value varchar(50) '@value')
I get a single row returned (with null values).
I've tried lots of other permutations, but nothing so far has worked. The only thing that has been modestly successful is to create and Edge Table (exclude the WITH clause), but that's really hard for me to work with. Another option is to insert a <line num="#">...<line> around each group of three elements (name, oper, value). That worked well, but it's a pain to implement (it means going back and updating all rows where the XML data exists and inserting the proper data.
I am by no means an XML guru, so if anyone has a suggestion, I'd love to hear it.
I don't know if anyone can help me but I have two issues with SQL Server 2000 SP4 (version 8.00.2039 - Desktop Engine) running on W2K and W2K3. I'm also running SQLXML 3.0 (msxml2.dll version is 8.30.9530.0).
Is it me or is sp_xml_preparedocument a crippled fat dog that is blind?...not that I have anything against crippled fat dogs that are blind :)
In all the stored procs I have developed, I pass a text var as an input parameter and return an IStream to ADO (using an sqlxml provider) in COM+. All has been very well and fine...until the passed text parameter resembles a data object of any decent size.
The first error I was noticing was a "XML Parsing Error: not enough storage is available to complete this operation". Well, I thought I would debug logically in a step fashion and just prepare the doc first and then do a return and then do a return on the next segment of code to find out where the issue is. I was amazed to find that sp_xml_preparedocument is taking 7 seconds to load a simple 1MByte text input var and around three minutes to load a 7 MByte file.
I believe these long load times are causing issues with transaction timouts etc so I thought I would try to solve the speed issue with sp_xml_preparedocument and then see if the "XML Parsing Error" continues.
So, my first question is:
Should sp_xml_preparedocument take 7 seconds to load a 1MByte text variable and nearly three odd minutes to load a 7 MByte file? Surely there is something wrong somewhere?
I'm also running these tests on two machines - one is 2 GHz and the other is 2.4 GHz P4's.
I have an variable size XML file currently +/- 5 MB(8000+ records) but may be bigger. I need to INSERT/UPDATE each row. Which is the better way of doing this? Using selectNodes on the XML and INSERT/UPDATE each row via a SP or using sp_xml_preparedocument and OPENXML therefore only calling the SP once?
I've got thousands of XML docs and have to import those to the DB. AndI am having problem with getting some values because of tricky XMLformat.I didn't create this XML format and I don't like this format, OOP snobdid. And I(DBA) have to look after their mess.I've got XML doc like this :<customer><customer_id>12345</customer_id><first_name>Jason</first_name><last_name>Varitek</last_name><location><city>Boston</city><state>MA</state></location></customer>First, I did following :DECLARE @idoc intDECLARE @doc varchar(1000)SET @doc ='<customer><customer_id>12345</customer_id><first_name>Jason</first_name><last_name>Varitek</last_name><location><city>Boston</city><state>MA</state></location></customer>'EXEC sp_xml_preparedocument @idoc OUTPUT, @docSELECT *FROM OPENXML (@idoc, '/customer',2)WITH (customer_id int,first_name varchar(50),last_name varchar(50),location varchar(50))Then returned this which is not bad.customer_id | first_name |last_name |location12345 | Jason | Varitek |Boston MABut I need to return city and state in separated columns(I wish OOPsnobs made these as attributes in location element, instead ofelements). And I tried following and returned everything NULL.DECLARE @idoc intDECLARE @doc varchar(1000)SET @doc ='<customer><customer_id>12345</customer_id><first_name>Jason</first_name><last_name>Varitek</last_name><location><city>Boston</city><state>MA</state></location></customer>'EXEC sp_xml_preparedocument @idoc OUTPUT, @docSELECT *FROM OPENXML (@idoc, '/customer/customer_id',2)WITH (customer_id int '../@customer_id',first_name varchar(50) '../@first_name',last_name varchar(50) '../@last_name',city varchar(50) '@city',state varchar(50) '@state')Does anybody have any idea to get value of city & state elements inseparated columns?ThanksJimmy
I am researching the use of OpenXml for doing mass updates/inserts.Does anyone know how this procedure works as far as sql injection isconcerned? I've always been taught to use sp's with parameters...doesusing OpenXML open up any holes in that idea?My thinking is that it would be fine (maybe even better), because thefields will still be treated as literals.Alternatively, are there any other suggestions for doing massiveamounts of updates/inserts?
Hi All,I want to pass XML and the data in the XML should be stored in thetables of the database. However, I do not want to use the OpenXMLstatement. Please let me know.Regards, Shilpa
The below code is only pulling the outcome_id value, but is pullingback null for the other fields in the xml string, any ideas on what isgoing on?-- Prepare xml data to be transfered into an xml table in sql serverDECLARE @xmlTable varchar(8000)DECLARE @DocHandle intselect @xmlTable = '<DATA><xmlRow outcome_id="35"dt_outcome="12/1/2004" patient_regimen_id="21" regimen_id="2"record_type="existing" /></DATA>'EXEC sp_xml_preparedocument @DocHandle OUTPUT, @xmlTableSELECT xmlTables.*FROM OPENXML (@DocHandle, '/DATA/xmlRow') WITH(outcome_id int '@outcome_id',patient varchar '@chvOutcomeDte') AS xmlTables-- Close the xml tableEXEC sp_xml_removedocument @DocHandleI did have this working in another stored procedure, but this one doesnot seem to want to work. I really need some help on this, thanks.
Code Snippet DECLARE @idoc int DECLARE @doc varchar(1000) SET @doc =' < ROOT > < Customer CustomerID="VINET" > < /Customer > < /ROOT > ' --Create an internal representation of the XML document. EXEC sp_xml_preparedocument @idoc OUTPUT, @doc -- Execute a SELECT statement that uses the OPENXML rowset provider. SELECT * FROM OPENXML (@idoc, '/ROOT/Customer',1) WITH (CustomerID varchar(10))
but how for this:
Code Snippet DECLARE @idoc int DECLARE @doc varchar(1000) SET @doc =' <ROOT> <Customer> <CustomerID>VINET</CustomerID> </Customer> </ROOT>' --Create an internal representation of the XML document. EXEC sp_xml_preparedocument @idoc OUTPUT, @doc -- Execute a SELECT statement that uses the OPENXML rowset provider. SELECT * FROM OPENXML (@idoc, '/ROOT/Customer',1) WITH (CustomerID varchar(10))
I am viewing a list of the reserved keywords for CE 3.5 and OPENXML is listed. Can you please tell me how I would implement this in CE 3.5? Since only one SQL statement can be sent with a SqlCeCommand then how can you prepare the document, access the xml data with OPENXML, and then close the document handle all in one call? For example, I have the following TSQL code in a SQL Express stored procedure and need to port it over to CE 3.5. Could you please help with some great ideas on how to go about this porting task?
CREATE PROCEDURE [SaveConfigurationMiscs]
(@XmlData Text)
AS
SET NOCOUNT ON
DECLARE @iDoc Int
/* Create an internal representation of the XML document. */
I am trying to import an xml file into a SQL 2005 table using sp_xml_prepareDocument ...OPENXML.
I always get 0 rows affected even though there is data in the file. The table structure is identical to the XML output. The OpenXML qry uses the following syntax:
FROM OPENXML(@xmlHndAdd, '/NewDataSet/Table1', 1) WITH MyTbl
insert into test select xmlID, xmlData from OpenXml(@i, 'mydata/test') with (xmlID int, xmlData nvarchar(30)) where xmlID not in (select xmlID from test)
update test set test.xmlData = ox.xmlData from OpenXml(@i, 'mydata/test') with (xmlID int, xmlData nvarchar(30)) ox where test.xmlID = ox.xmlID
exec sp_xml_removedocument @i
My question is when running this stored procedure for the first time, isn't one inserting AND then updating each row?
INSERT INTO Books (BookName) SELECT BookName FROM OPENXML (@hDoc, '/Objects/Book', 2) WITH (BooKName nvarchar(255)) xmlBookss WHERE NOT EXISTS (SELECT ID FROM Books WHERE Books.BookName = xmlBooks.BookName)
Where my XML looks like:
<Objects> <Book> <ForeignID>876</ForeignID> <BookName>SQLTeam Rocks</BookName> </Book> <Book> <ForeignID>981</ForeignID> <BookName>My SQL must get better</BookName> </Book> </Objects>
When I insert run the query I want to get the @@IDENTITY for each row and insert them with the corresponding ForeignID into a lookup table structured:
