OPENXML Insert/Update

Mar 11, 2008

Hi All,

The code (http://sqlxml.org/faqs.aspx?faq=100) below mirrors mine but is simplfied:

declare @i int

exec sp_xml_preparedocument @i output,
'<mydata>
<test xmlID="3" xmlData="blah blah blah"/>
<test xmlID="1" xmlData="blah"/>
</mydata>'

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?

View 1 Replies


ADVERTISEMENT

OPENXML Insert/Output

Mar 11, 2008

Hi Guru's,


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:

[ID Auto], [BooksID (from Books table)], [ForeignID (from XML)]

Any thoughts appreciated!

View 13 Replies View Related

SQL Server 2008 :: Update Null Enabled Field Without Interfering With Rest Of INSERT / UPDATE

Apr 16, 2015

If I have a table with 1 or more Nullable fields and I want to make sure that when an INSERT or UPDATE occurs and one or more of these fields are left to NULL either explicitly or implicitly is there I can set these to non-null values without interfering with the INSERT or UPDATE in as far as the other fields in the table?

EXAMPLE:

CREATE TABLE dbo.MYTABLE(
ID NUMERIC(18,0) IDENTITY(1,1) NOT NULL,
FirstName VARCHAR(50) NULL,
LastName VARCHAR(50) NULL,

[Code] ....

If an INSERT looks like any of the following what can I do to change the NULL being assigned to DateAdded to a real date, preferable the value of GetDate() at the time of the insert? I've heard of INSTEAD of Triggers but I'm not trying tto over rise the entire INSERT or update just the on (maybe 2) fields that are being left as null or explicitly set to null. The same would apply for any UPDATE where DateModified is not specified or explicitly set to NULL. I would want to change it so that DateModified is not null on any UPDATE.

INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
VALUES('John','Smith',NULL)

INSERT INTO dbo.MYTABLE( FirstName, LastName)
VALUES('John','Smith')

INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
SELECT FirstName, LastName, NULL
FROM MYOTHERTABLE

View 9 Replies View Related

Can I Insert/Update Large Text Field To Database Without Bulk Insert?

Nov 14, 2007

I have a web form with a text field that needs to take in as much as the user decides to type and insert it into an nvarchar(max) field in the database behind.  I've tried using the new .write() method in my update statement, but it cuts off the text after a while.  Is there a way to insert/update in SQL 2005 this without resorting to Bulk Insert? It bloats the transaction log and turning the logging off requires a call to sp_dboptions (or a straight-up ALTER DATABASE), which I'd like to avoid if I can.

View 6 Replies View Related

T-SQL (SS2K8) :: Insert / Update Triggers When Insert Run Via Script

Oct 23, 2014

I'm working on inserting data into a table in a database. The table has two separate triggers, one for insert and one for update (I don't like it this way, but that's how it's been for years). When there is a normal insert, done via a program, it looks like the triggers work fine. When I run an insert manually via a script, the first insert trigger will run, but the update trigger will fail. I narrowed down the issue to a root cause.

This root issue is due to both triggers using the same temporary table name. When the second trigger runs, there's an error stating that a few columns don't exist. I went to my test server and test db and changed the update trigger so that the temporary table is different than the insert trigger temporary table, the triggers work fine. The weird thing is that if the temporary table already exists, when the second trigger tries to create the temporary table, I would expect it to fail and say that it already exists.I'm probably just going to update the trigger tonight and change the temporary table name.

View 1 Replies View Related

Trigger To Update A Table On Insert Or Update

Feb 15, 2008



Hello

I've to write an trigger for the following action

When a entry is done in the table Adoscat79 having in the index field Statut_tiers the valeur 1 and a date in data_cloture for a customer xyz

all the entries in the same table where the no_tiers is the same as the one entered (many entriers) should have those both field updated

statut_tiers to 1
and date_cloture to the same date as entered

the same action has to be done when an update is done and the valeur is set to 1 for the statut_tiers and a date entered in the field date_clture

thank you for your help
I've never done a trigger before

View 14 Replies View Related

Single Complex INSERT Or INSERT Plus UPDATE

Jul 23, 2005

Hello,I am writing a stored procedure that will take data from severaldifferent tables and will combine the data into a single table for ourdata warehouse. It is mostly pretty straightforward stuff, but there isone issue that I am not sure how to handle.The resulting table has a column that is an ugly concatenation fromseveral columns in the source. I didn't design this and I can't huntdown and kill the person who did, so that option is out. Here is asimplified version of what I'm trying to do:CREATE TABLE Source (grp_id INT NOT NULL,mbr_id DECIMAL(18, 0) NOT NULL,birth_date DATETIME NULL,gender_code CHAR(1) NOT NULL,ssn CHAR(9) NOT NULL )GOALTER TABLE SourceADD CONSTRAINT PK_SourcePRIMARY KEY CLUSTERED (grp_id, mbr_id)GOCREATE TABLE Destination (grp_id INT NOT NULL,mbr_id DECIMAL(18, 0) NOT NULL,birth_date DATETIME NULL,gender_code CHAR(1) NOT NULL,member_ssn CHAR(9) NOT NULL,subscriber_ssn CHAR(9) NOT NULL )GOALTER TABLE DestinationADD CONSTRAINT PK_DestinationPRIMARY KEY CLUSTERED (grp_id, mbr_id)GOThe member_ssn is the ssn for the row being imported. Each member alsohas a subscriber (think of it as a parent-child kind of relationship)where the first 9 characters of the mbr_id (as a zero-padded string)match and the last two are "00". For example, given the followingmbr_id values:1234567890012345678901123456789021111111110022222222200They would have the following subscribers:mbr_id subscriber mbr_id12345678900 1234567890012345678901 1234567890012345678902 1234567890011111111100 1111111110022222222200 22222222200So, for the subscriber_ssn I need to find the subscriber using theabove rule and fill in that ssn.I have a couple of ideas on how I might do this, but I'm wondering ifanyone has tackled a similar situation and how you solved it.The current system does an insert with an additional column for thesubscriber mbr_id then it updates the table using that column to joinback to the source. I could also join the source to itself in the firstplace to fill it in without the extra update, but I'm not sure if theextra complexity of the insert statement would offset any gains fromputting it all into one statement. I plan to test that on Monday.Thanks for any ideas that you might have.-Tom.

View 4 Replies View Related

Can I Roll Back Certain Query(insert/update) Execution In One Page If Query (insert/update) In Other Page Execution Fails In Asp.net

Mar 1, 2007

Can I roll back certain query(insert/update) execution in one page if  query (insert/update) in other page  execution fails in asp.net.( I am using sqlserver 2000 as back end)
 scenario
In a webpage1, I have insert query  into master table and Page2 I have insert query to store data in sub table.
 I need to rollback the insert command execution for sub table ,if insert command to master table in web page1 is failed. (Query in webpage2 executes first, then only the query in webpage1) Can I use System. Transaction to solve this? Thanks in advance

View 2 Replies View Related

OpenXML

Nov 19, 2007

Hi

I've run into a problem using openxml and would greatly appreciate any help or direction!

The Xml document I am working with contains the following excerpt:


<Address>
<UKAddress>
<no>24</no>
<Line>A</Line>
<Line>B</Line>
<PostCode>GIR 0AA</PostCode>
</UKAddress>
</Address>

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

Here's the code for my sproc....

.
...
.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

SELECT *
FROM OPENXML (@idoc, 'root/data/', 2)
WITH (Number int '//no',
// address line detail here
Postcode varchar(10) '//PostCode')
.
...
.

I'm ok with OpenXML/sp_xml_preparedocument syntax and that, but I can't think how to handle unknown multiple address lines with the same tag name!

ANY HELP GREATLY RECEIVED!!!! :)

View 2 Replies View Related

OPENXML Vs BCP

Oct 3, 2005

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

View 1 Replies View Related

Using OPENXML

Jul 20, 2005

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.

View 2 Replies View Related

Why OPENXML Returns Only First Row?

Aug 11, 2006

 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

View 1 Replies View Related

SQL OPENXML: Best Approach

Feb 27, 2008

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
 

View 2 Replies View Related

XML - Openxml With SQL-Server

Feb 13, 2004

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

View 1 Replies View Related

OPENXML Problem

Sep 22, 2005

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

View 1 Replies View Related

OpenXML DataType

Sep 30, 2005

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

View 3 Replies View Related

OPENXML Error

Jan 20, 2006

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))"

View 5 Replies View Related

Urgent Please Help - OpenXML

Aug 6, 2005

Hi,
The Code is :

select @pvoSql = 'INSERT INTO ' + @pOrderDB +'.dbo.tx_customer_det '

+ '(s_lastname)' +

+ ' SELECT customer_lastname' +

+ ' FROM ' +

+ ' OPENXML ( '+@DocHandle + ', ''/xml/datacapture/orders/order/generic_customer_info'',2) With (customer_lastname varchar(30))'

exec (@pvoSql)



I get an Error saying : Error converting varchar to int @ docHandle

View 1 Replies View Related

OPENXML Question

Oct 10, 2006

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.

Regards,

hmscott

View 6 Replies View Related

OpenXML Woes - 2Q's

Dec 16, 2006

Hi everybody.

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.

Cheers and thanks for any info.

Erron

View 2 Replies View Related

Performance OpenXML Vs Row By Row

Mar 11, 2008

Hi All,

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?

View 1 Replies View Related

Error With OPENXML

Jul 23, 2005

I am getting an error(XML parsing error: Not enough storage isavailable to complete this operation.) while using OPENXML on server,I think the problem is with bad version of MSXML.dll (someone said Imight be using 2.6 and NOT 3.0 Parser).I want to know the service pack version of the SQL 2000 so that I wouldthen decide whether to apply SP3 or SP3a.I would really appreciate if anyone can provide feedback on this.Select @@version gives me this.Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 200000:57:48 Copyright (c) 1988-2000 Microsoft Corporation EnterpriseEdition on Windows NT 5.0 (Build 2195: Service Pack 4)xp_msver gives me this1ProductNameNULLMicrosoft SQL Server2ProductVersion5242888.00.1943Language1033English (United States)4PlatformNULLNT INTEL X865CommentsNULLNT INTEL X866CompanyNameNULLMicrosoft Corporation7FileDescriptionNULLSQL Server Windows NT8FileVersionNULL2000.080.0194.009InternalNameNULLSQLSERVR10LegalCopyrightNULL© 1988-2000 Microsoft Corp. All rightsreserved.11LegalTrademarksNULLMicrosoft® is a registered trademark ofMicrosoft Corporation. Windows(TM) is a trademark of MicrosoftCorporation12OriginalFilenameNULLSQLSERVR.EXE13PrivateBuildNULLNULL14SpecialBuild65630NULL15WindowsVersion1438515255.0 (2195)16ProcessorCount2217ProcessorActiveMask30000000318ProcessorType586PROCESSOR_INTEL_PENTIUM19PhysicalMemory20472047 (2146942976)20Product IDNULLNULL

View 5 Replies View Related

OPENXML Question

Jul 23, 2005

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

View 2 Replies View Related

SQLInjection With OpenXML

Mar 9, 2006

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?

View 1 Replies View Related

Alternative To OpenXML

Mar 17, 2006

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

View 2 Replies View Related

OpenXML Query

Jul 20, 2005

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.

View 1 Replies View Related

Different XML Format For OpenXML

Apr 25, 2008

I can do this for this XML :



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))

View 5 Replies View Related

OPENXML With Dynamic SQL

Sep 5, 2007



I am trying to use OPENXML in a dynamic query and i guess its croaking becaause it starts a new thread and so it does not know about the file handle

any workaorounds

I can paste the snippet of code if needed

Thanks

View 2 Replies View Related

OPENXML Supported In CE 3.5?

Oct 2, 2007



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. */


EXEC sp_xml_preparedocument @iDoc OUTPUT, @XmlData



/* Dump XML Data into an in memory table */

DECLARE @ConfigurationMiscs TABLE (

ConfigurationMiscID Int,

ActiveLaunchMonitor NVarChar(50),

UseModeledShots Bit,

DeleteRecord Int

)



INSERT INTO @ConfigurationMiscs (ConfigurationMiscID, ActiveLaunchMonitor, UseModeledShots, DeleteRecord)

SELECT X.ConfigurationMiscID, X.ActiveLaunchMonitor, X.UseModeledShots, X.DeleteRecord

FROM OPENXML (@iDoc, '/xml/ConfigurationMisc')

WITH (

ConfigurationMiscID Int,

ActiveLaunchMonitor NVarChar(50),

UseModeledShots Bit,

DeleteRecord Int

) X



/* Clean up XML resources */

EXEC sp_xml_removedocument @iDoc


/* Update */

UPDATE Table_ConfigurationMiscs

SET

Table_ConfigurationMiscs.ActiveLaunchMonitor = X.ActiveLaunchMonitor,

Table_ConfigurationMiscs.UseModeledShots = X.UseModeledShots

FROM ConfigurationMiscs Table_ConfigurationMiscs

INNER JOIN

@ConfigurationMiscs X ON Table_ConfigurationMiscs.ConfigurationMiscID = X.ConfigurationMiscID

WHERE

X.ActiveLaunchMonitor IS NOT NULL AND

X.UseModeledShots IS NOT NULL




/* Insert New */

INSERT INTO ConfigurationMiscs (ConfigurationMiscID, ActiveLaunchMonitor, UseModeledShots)

SELECT X.ConfigurationMiscID, X.ActiveLaunchMonitor, X.UseModeledShots

FROM @ConfigurationMiscs X

LEFT JOIN

ConfigurationMiscs Table_ConfigurationMiscs ON Table_ConfigurationMiscs.ConfigurationMiscID = X.ConfigurationMiscID

WHERE

Table_ConfigurationMiscs.ConfigurationMiscID IS NULL AND

X.ActiveLaunchMonitor IS NOT NULL AND

X.UseModeledShots IS NOT NULL


/* Delete Old */

DELETE Table_ConfigurationMiscs

FROM ConfigurationMiscs Table_ConfigurationMiscs

INNER JOIN

@ConfigurationMiscs X ON Table_ConfigurationMiscs.ConfigurationMiscID = X.ConfigurationMiscID

WHERE

X.ConfigurationMiscID IS NOT NULL AND

X.DeleteRecord IS NOT NULL



RETURN

GO

View 1 Replies View Related

OPENXML Question

Oct 13, 2006

Hi!

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

The XML file format is:

<NewDataSet xmlns="">
<Table1 diffgr:id="Table11" msdata:rowOrder="0">
<program_id>1-2-3-4-5</program_id>
<object_name />
</Table1>
<Table1 diffgr:id="Table12" msdata:rowOrder="1">
<object_id>6-7-8-9-0</object_id>
<object_name>ABC</object_name>
<objectproperty_id>1-3-5-7-9</objectproperty_id>
</Table1>

Any suggestions are greatly appreciated!!!

Thank you!

View 5 Replies View Related

OpenXML Multiple Namespaces

Nov 19, 2007

Hi

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.

Here's (a simplified version) of my code...

--------------------------------------------------------------------

DECLARE @idoc INT
DECLARE @doc varchar (8000)

SET @doc ='
<?xml version="1.0" encoding="UTF-8"?>
<LHA_DATA xmlns="http://www.trs.co.uk/schema/DataShare.xsd" xmlns:cmn="http://www.trs.co.uk/schema/Common_ed.xsd">
<Manifest>
<cmn:senderRecipient>
<cmn:LACode>A0000</cmn:LACode>
<cmn:OfficeCode>0</cmn:OfficeCode>
</cmn:senderRecipient>
</Manifest>
</LHA_DATA>
'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc, '<LHA_DATA xmlns:cmn="xmlns="http://www.trs.co.uk/schema/DataShare.xsd" http://www.trs.co.uk/schema/Common_ed.xsd" />'


SELECT *
FROM OPENXML (@idoc, 'LHA_DATA', 2)
WITH (LACode varchar(10) '//cmn:LACode',
OfficeCode varchar(10) '//cmn:OfficeCode')

EXEC sp_xml_removedocument @idoc

------------------------------------------------------------------------

Any help greatly received!!!!

View 1 Replies View Related

Openxml Rowset Problem

Jan 12, 2004

hi all,

I'm trying to get some xml data into sql server but i ran into this problem: openxml seems to repeat results ...

I simplified my example to this:

------------------------------------------------------
Declare @rDoc int,
@sDoc varchar(4000)

Set @sDoc = '
<ROOT>
<Rider>aaa</Rider>
<Rider>bbb</Rider>
<Rider>ccc</Rider>
</ROOT>'

EXEC sp_xml_preparedocument @rDoc OUTPUT, @sDoc

SELECT *
FROM OPENXML (@rDoc, 'ROOT/Rider', 1)
WITH (RiderName varchar(50) '../Rider')
----------------------------------------------------------

it returns 3 records for the 'rider' elements, _but_ all the records contain the text 'aaa' :/

does anybody know the solution to this?

cheers,

alex

View 2 Replies View Related

Maximum Size Of OPENXML IN SP

May 17, 2006



How much data we can pass through as an XML Text into SP by the concept OPENXML

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved