Recent update: 2008-3-21
I need insert many dates from tables to XML file, I use T-SQL, everything is OK, but in one step I can insert maximally 4000 character in uni-code. Is there another way? Thanks' a lot. I use such sequence T-SQL: Declare @doc XML, @ixml nvarchar(4000) SELECT @ixml=(SELECT * FROM SAMPLE ...
4388 byte By
CUIWEI at 2008-3-6
I store the xml info below in the XML field in SQL 2005, I hope to query all the Folder node with XQuery. The result I hope to get <Folder Name="Root" Id="a6dce8fe-749c-4e38-ab2f-3d03d9711b3d"> <Folder Name="Card" Id="b8dcf8fe-749c-4e38-ab2f-6d03d9711b8j"> ...
1664 byte By
aero1 at 2008-3-5
I have a number of tables with columns of xml datatype. Each of these columns are typed against a different XML schema collection. However, each of the XML schema collections contain a hierarchy of schema definitions - and the schemas towards the top of the hierarchy are used by a number of ...
15468 byte By
Eisa at 2008-3-3
Hi all i've a question about quering an xml document contains a multilevel xml nodes and i want to retrive only the first level without all the subnodes below is a snippet of the document - <xml> - <CfgPlace DBID="2907" tenantDBID="101" ownerDBID="101" name="Place No 1" ...
714 byte By
JayH at 2008-3-1
I am trying to insert multiple nodes to an xml column using an update statement from a joined table. For (a simplified) example:UPDATE DocumentsSET DocumentDetails.modify('insert <DocumentParty name="{sql:column(''n.Name'')}"/> ...')FROM Documents d INNER JOIN DocumentNameXRef dnx ...
I have two views listed below that get attributes at there respective levels ‘//BHILeads/Lead/Contact’ and ‘//BHILeads/Lead/PropertyInterest'. What I would like to do is make one view, but I don’t know how. I looked at the examples in AdventureWorks and they all use target name spaces. Do I ...
102 byte By
CUIWEI at 2008-2-27
I feel when I operate XML field, it's so slow! no matter insert or delete e a record. how about you?
hello! i'm working on a project for pocket pc, which is an app that registers customers, suppliers, stocks and orders. all this is stored in the ppc itself using the SQL CE server. i'm programming with VS2005, which is great, the tableadapters make the work lot easier. i'm just having some ...
Hi ... I am attempting to use the sql:relation annotation and am having some troubles. I have used it successfull with the MS Sample (Customer / Order) where all the data is contained in elements (example below). <ROOT> <Customers> <CustomerID>1111</CustomerID> ...
01 Dec 2005 Saint Paul, Minnesota macgowan@pobox.com Hi ... I am trying to bulk load xml into sql server. This seems to be a relativly easy thing to do using the SQLXML COM Component. I am having trouble finding documentation on the SQLXML COM Object model. Also I have found samples in C# and ...
Hi ... I am trying to load some common data from the element "site" into two tables (MacgowanTestRWISRawAtmospheric and MacgowanTestRWISRawSurface). The data in attributes "sysid" and "rpuid" need to be copied to both tables. Is there a way to do this with the schema file? . The two tables ...
Hi ... I have a program that will insert xml data into a table. When I add an IDENTITY column to the table then I get the following error: ... [Cannot insert the value NULL into column 'RecordId', table 'Alphanumericdata.dbo.MacgowanTestCust'; column does not allow nulls. INSERT fails.] ...
695 byte By
Richo at 2008-2-22
Hi,I have a C# application that successfully creates an instance of SQLXMLBulkLoad3Class and executes with error. I can also see in the SQL Server trace that there is activity, (i.e. A bunch of insert statements for the tables that I would expect given the XML and XSD files). Problem is no data ...
I have a several xml files to import in a sql 2005 xml datatype column with the OPENROWSET BULKLOAD.The xml files declares an external dtd.I must use the CONVERT option 2 to avoid an internal dtd error.When I try to read the column, sql throws a Lists of BinaryXml value tokens not supported ...
363 byte By
tredd at 2008-2-21
Hi,I am trying to shred a large number of XML files from a directory into a SQL Server 2005 table, having not really used xml before and having little experience of SSIS which seems to be the preferred tool to do this, does anyone have a really easy step by step guide that will break me into this so...
I have been using VB Express 2005 and SQL Server Express 2005 over the last week and converting one of my projects from Visual Studio 2003 and SQL Server 2000.I use the Managed Classes(SqlXmlCommand, SqlXmlAdapter), XPath Queries, DataSets to query the database. I want to use the new data ...
I've installed the SQL Server 2005 Express and also the MS SQL Server Management Studio 2005 ExpressBut when i load the latter and try to connect to my database I get the below error message.TITLE: Connect to ServerCannot connect to DEVCOMP\SQLEXPRESS.ADDITIONAL INFORMATION:An error has ...
Hi ... I need to bulk insert the xml data below. I am working on some issues related to the Itentity column (you may have read the other question). The xml below uses elements with attributes. Is the schema file formated correctly to extract the attributes from the SITE element and then also ...
I run a web service query daily. The entire resulting web service XML response is saved into an xml column in a new row each day. The results from one day to the next will largely overlap, save status updates, etc. For example, the results on one day might be:<TransactionArray> ...
5954 byte By
aero1 at 2008-2-18
In summary my problem is:- I have two schemas 'Parent' and 'child'- The child schema extends the parent schema by adding one attribute and redefining an attribute from the parent- VB.Net permits the two dependent schema definitions in a schemaset- Creating the two schemas in a SQL2005 xml ...
685 byte By
cssrk at 2008-2-17
Hi, I have been working on SQL Server 2005 nearly for an year now. In Beta2, there is class XMLBulkLoad which can be used to bulk copy data from an XML file into SQL Server using a mapping schema. Now I have installed the Yukon April CTP and OMG the XMLBulkLoad class is missing. Does anybody ...
Hi All,.I am trying to open and read an XML web page directly from SQL Server 2000.I have found various articles on preparing and opening an XML document ( http://www.perfectxml.com/articles/xml/importxmlsql.asp and http://techrepublic.com.com/5100-9592-5796532.html#) but both presume the XML ...
Hello,I have a very simple data table: CREATE TABLE [ALMPayloads]([ID] [int] NOT NULL,[OutputPayload] [xml] NOT NULL)with the following content:ID = 1 OutputPayload:<ReportDocument> <ALMSimulationResult> <selectedModelAssets> <modelAsset ...
Hi ... I need to bulk insert the xml data below. I am working on some issues related to the Itentity column (you may have read the other question). The xml below uses elements with attributes. Is the schema file formated correctly to extract the attributes from the SITE element and then also ...
Hi ... I have a program that will insert xml data into a table. When I add an IDENTITY column to the table then I get the following error: ... [Cannot insert the value NULL into column 'RecordId', table 'Alphanumericdata.dbo.MacgowanTestCust'; column does not allow nulls. INSERT fails.] ...
Hello,I have a very simple data table: CREATE TABLE [ALMPayloads]([ID] [int] NOT NULL,[OutputPayload] [xml] NOT NULL) with the following content:ID = 1 OutputPayload:<ReportDocument> <ALMSimulationResult> <selectedModelAssets> <modelAsset ...
Hello,running the following code:declare @x xmlset @x='<ManuInstructions ProductModelID="1" ProductModelName="SomeBike" ><Location LocationID="L1" LocationName="Name 1" /><Location LocationID="L2" LocationName="Name 2" /></ManuInstructions>'SELECT @x.query(' for ...
I've installed the SQL Server 2005 Express and also the MS SQL Server Management Studio 2005 ExpressBut when i load the latter and try to connect to my database I get the below error message.TITLE: Connect to ServerCannot connect to DEVCOMP\SQLEXPRESS.ADDITIONAL INFORMATION:An error has ...
I've installed the SQL Server 2005 Express and also the MS SQL Server Management Studio 2005 ExpressBut when i load the latter and try to connect to my database I get the below error message.TITLE: Connect to ServerCannot connect to DEVCOMP\SQLEXPRESS.ADDITIONAL INFORMATION:An error has ...
I have been using VB Express 2005 and SQL Server Express 2005 over the last week and converting one of my projects from Visual Studio 2003 and SQL Server 2000.I use the Managed Classes(SqlXmlCommand, SqlXmlAdapter), XPath Queries, DataSets to query the database. I want to use the new data ...
I run a web service query daily. The entire resulting web service XML response is saved into an xml column in a new row each day. The results from one day to the next will largely overlap, save status updates, etc. For example, the results on one day might be:<TransactionArray> ...
Hi all, I am making a import from XML to SQL. I have chosen to do this via a XSD file, but I have a problem with the ROOT file of the XMLXML:<Dogs bulkmode="0" xmlns=" http://scheme01.isp.com"> <UnderDogs> ... </UnderDogs></Dogs>XSD:<xsd:element name="Dogs bulkmode="0" ...
I'm having difficulties running the queries I'd like to against xml type columns containing SOAP response envelopes from web service calls I'm making. I've reduced the problem to a fairly small representative example:declare @testxml XMLset @testxml='<soapenv:Envelope ...
01 Dec 2005 Saint Paul, Minnesota macgowan@pobox.com Hi ... I am trying to bulk load xml into sql server. This seems to be a relativly easy thing to do using the SQLXML COM Component. I am having trouble finding documentation on the SQLXML COM Object model. Also I have found samples in C# and ...
Hi!When I try load the following XML:DECLARE @idoc intDECLARE @doc varchar(8000)SET @doc = '<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?><Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" ...
862 byte By
KevinS at 2008-2-8
I am inserting into a table using openxml as follows SET @dynamic_sql = N'INSERT INTO ' + @table_name + ' SELECT * FROM OPENXML (@xml_data_doc_se, ''//' + @node_name + ', 2) WITH ' + @table_name; SET @dynamic_sql_params = N'@xml_data_doc_se INT'; -- Run the dynamic SQL EXECUTE ...
Hi,I have a table with a column as XML datatype. Pl find here the script belowcreate table books( bookid int, bookdets xml) insert into books values (1, '<book><title>First ...
Does anyone know how XQuery/XPath would work with element substition ? For example, if a schema had a /customer/name element, and a substitution of /kunde/navn, would looking for /customer/name find documents with either of /customer/name or kunde/navn ?e.g. schema fragment:<xs:element ...
I want to add perent element to existing element in xml variable (SQL 2005). I solve that problem with: DECLARE @a xml SET @a = N'<EVENT_INSTANCE><a>CREATE_TYPE</a><b1>2005-10-14T01:58:28.070</b1><c>51</c></EVENT_INSTANCE>' SELECT @a SET ...
Hi,I am trying to make some stored procedures that take in lists of well sites and well ids and find the wells in the sites except for those in the list of well ids.I have a stored procedure like this.ALTER procedure sp_wellSelect (@criteria ntext) as DECLARE @hDoc int--Prepare input values as ...
2827 byte By
PugV at 2008-2-7
just started playing with xml datatype, and I'm running into a performance issue...I have to query an xml column which was populated by a 'for xml path' statement, and get the values back into relational tables... select DeletedData.value('(/row/ListingID)[1]','int') as ListingID, ...
Hi,I am trying to make some stored procedures that take in lists of well sites and well ids and find the wells in the sites except for those in the list of well ids.I have a stored procedure like this.ALTER procedure sp_wellSelect (@criteria ntext) as DECLARE @hDoc int--Prepare input values as ...
I'm looking for the "proper" way to do this:I've been given a web site that generates some XML. I need to load the XML from the site (that's trivial, of course) and then populate a database table with the information contained in the XML.For example, the XML looks something like ...
4415 byte By
LarsK at 2008-2-5
I am presently taking my first, tremling and insecure steps in Sql Server as a whole - specifically Sql Server 2005. I am involved in a proposal for a huge data convserion project (banking) here in Denmark and presently trying to present small mockups to illustrate the potential of XML in ...
I'm looking for the "proper" way to do this:I've been given a web site that generates some XML. I need to load the XML from the site (that's trivial, of course) and then populate a database table with the information contained in the XML.For example, the XML looks something like ...
4416 byte By
LarsK at 2008-2-4
I am presently taking my first, tremling and insecure steps in Sql Server as a whole - specifically Sql Server 2005. I am involved in a proposal for a huge data convserion project (banking) here in Denmark and presently trying to present small mockups to illustrate the potential of XML in ...
2560 byte By
MRys at 2008-2-4
Welcome dear seeker of XML knowledgePlease take some time to read this welcome message that can help you find your answer quicker.Please take a look at previous postings to see whether your question already has been asked and answered before. Also please check out some of the online resources ...
2828 byte By
PugV at 2008-2-4
just started playing with xml datatype, and I'm running into a performance issue...I have to query an xml column which was populated by a 'for xml path' statement, and get the values back into relational tables... select DeletedData.value('(/row/ListingID)[1]','int') as ListingID, ...
Hi:I have xml in sql server 2005 beta2 that is stored as an xml data type and I'm trying to run some aggregate functions like count and sum, but I don't know how to do it. The elements are numeric values, but 'sum' and also 'count' return errors. I have included the error message and the ...
Hey All, I wanted to build a simple scheduled task that hits my document library table in SQL Server, builds an RSS (XML) file based on that table, and then saves the resulting XML as a flat .rss file. I'm using SQL Server 2000, and so far I've gotten SQL Server to output the XML from the ...