.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Post New Web Links

OpenXML and CR

Posted By:      Posted Date: October 18, 2010    Points: 0   Category :Sql Server
 

I try to get from XML with OpenXML data which contains CR character but it skips it. The code is below:

-- The destination table<br/>create table dbo.xml_TestImport
(
	HID int primary key,
	HDefinition varchar(8000)
)
GO
-- XML source
<br/>declare @XML_Doc varchar(8000)
set @XML_Doc = 
'<?xml version="1.0" encoding="ISO-8859-1"?><SRS><xml_TestImport HID="2" HDefinition="A&#x0D;&#x0A;&#x0D;&#x0A;C"/></SRS>'

	DECLARE @hdoc int	
	EXEC sp_xml_preparedocument @hdoc OUTPUT, @XML_Doc

	INSERT xml_TestImport 
			(	[HID]
				,[HDefinition]
			)
			SELECT  UpdData.[HID]
				,UpdData.[HDefinition]
				<


View Complete Post


More Related Resource Links

OpenXML Add commnet to Spreadsheet

  

I'm trying to add comments to cells in an Excel 2007 spreadsheet. I'm using the OpenXml SDK 2.0 to do so.

My use case is, I am editing Excel file and adding comments as my starting point,

Problem is that when I add Comment to the Comments part the spreadsheet doesn't load and Excel asks if I want to recover. Comments I added programmatically are not showing.

here is my code

                    WorksheetCommentsPart commentsPart = worksheetpart.AddNewPart<WorksheetCommentsPart>();
                    ssXML.Comments comments1 = new ssXML.Comments();

                    ssXML.Authors authors1 = new ssXML.Authors();
                    ssXML.Author author1 = new ssXML.Author();
                    author1.Text = "Author";

The XML Files: OPENXML, XSLT Keys, Select versus Match, XPath, and More

  

How to use OPENXML to load XML data into existing SQL Table?

  
I am a newbie to OPENXML. But I am trying to load a .XML file into a SQL table that I created for this. I do not receive any errors with this code, but it doesn't insert any records either. This is the table I created in 2008 SQL Server: CREATE TABLE HOMEROOM( HOMEROOM_TEACHER INT, HOMEROOM_NUMBER INT, ENTITY_ID INT) And this is the T-SQL code I am trying to execute: DECLARE @idoc int DECLARE @xmlDocument varchar(MAX) DECLARE @Status INT SET @xmlDocument =' <xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema"> <s:Schema id="RowsetSchema"> <s:ElementType name="row" content="eltOnly"> <s:AttributeType name="c0" rs:name="HOMEROOM-TEACHER" rs:number="1" rs:nullable="true"> <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true" /> </s:AttributeType> <s:AttributeType name="c1" rs:name="HOMEROOM-NUMBER" rs:number="2"> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="10" rs:maybenull="false" /> </s:AttributeType> <s:Attri

OPENXML Problem

  
I have a list id ID numbers contained in an xml document which i pass into a stored procedure. I want to change the a column to a set value for ALL the id's contained in the xml list, but for some reason I cant get it to work.  heres the sql  in the stored procedure EXEC sp_xml_preparedocument @idoc OUTPUT, @_xml BEGIN TRANSACTION UPDATE Table1  SET StatusID = @UpdateStatus FROM OPENXML(@idoc,'/Root/id',1) WITH (id bigint) ox WHERE Table1.ID = ox.id EXEC sp_xml_removedocument @idoc the sp compiles ok and is created, yet when I pass in the xml list, nothing is updated.  Can anyone see what im doing wrong here ?

Possible to Control Depth That OPENXML Parses xml document

  
I'm trying to parse a survey and only want to parse to a particular depth in the XML.  I want to preserve the hierarchical structure so that I can import it into SSAS and build a cube solution around the survey and the survey results. The structure of the xml follows this patten: <Survey Name="Medicare Inbound/Outbound Disenrollment Survey NY/NJ English"> <Introduction Text_English="Created by dcho on 3/23/2010." /> <   Question Tag="Inbound/Outbound" Type="Choice" Min="0" Max="40" Choices="inbound|outbound" Hint="" Question_English="(Select Inbound/Outbound and click Next)" /> <   Question Tag="Outbound Introduction" Type="Choice" Min="0" Max="40" Choices="yes|no" Hint="" Question_English="Hello may I speak with (disenrolled member)?&#xD;&#xA;&#xD;&#xA;(Select choice from dropdown and click Next)"> <   Condition Tag="Inbound/Outbound" Comparison="EQ" Value="outbound" /> </   Question> I only want to descend to the Question attribute so that I can pick up its Tag element.  I do not want to pick up the Condition attribute and its corresponding Tag element. I am using the following query,

need to compare two word 2007 documents using openxml

  
 Hi,Can some one help me, how to compare two word 2007 documents using openxml and c#. I need to change color for the differences in the compared document. Plz suggest some thing here ....Thanks,Ramakrishna

Export to Excel using OpenXML 2.0

  
I know I know, there are many threads on exporting to excel, though I haven't seen any going this route. I'm going off the solution in the following link, though there are a few things I need that are different then what is shown. http://blogs.msdn.com/b/brian_jones/archive/2008/11/04/document-assembly-solution-for-spreadsheetml.aspx    The solution shown relys on a "Template" file to be location on the client machine, and Hard Codes the Save Path and File Name. This works, and produces the perfect file in the end, but I need the user to be prompted with the standard Save As diag option. As you'd see with other methods where you export in the form of CSV file. //Make a copy of the template file File.Copy("template.xlsx", "generated.xlsx", true); //Open up the copied template workbook using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open("generated.xlsx", true)) {   This initial code snippit results in the site taking template.xlsx and creating 'Generated.xlxs' saving it at C:\Program Files (x86)\Common Files\microsoft shared\DevServer\10.0 (not really user friendly) So, A) How do I have the template either stored on the website, or Created on the Fly? B) How do I prompt the user for the File Name and Location where they want to save the file?   Thanks!

Problem when Inserting xml data into a table using OpenXml

  
Hi, i stuck with a problemam trying to insert data from xml file to sql server.i made query like thisSET NOCOUNT ON DECLARE @hDoc INT EXEC sp_xml_preparedocument @hDoc OUTPUT, '<NewDataSet>  <xs:schema id=''NewDataSet'' xmlns='''' xmlns:xs=''http://www.w3.org/2001/XMLSchema'' xmlns:msdata=''urn:schemas-microsoft-com:xml-msdata''>  <xs:element name=''Job''>     <xs:complexType>      <xs:sequence>        <xs:element name=''BillToNo'' type=''xs:string'' minOccurs=''0'' /> <xs:element name=''Colors'' type=''xs:string'' minOccurs=''0'' /> <xs:element name=''CompanyNo'' type=''xs:string'' minOccurs=''0'' /> <xs:element name=''CsrCode'' type=''xs:string'' minOccurs=''0'' /> <xs:element name=''CsrName'' type=''xs:string'' minOccurs=''0'' /> <xs:element name=''CustomerPoNo'' type=''xs:string'' minOccurs=''0'' /> <xs:element name=''DivisionOriginal'' type=''xs:string'' minOccurs=''0'' /> <xs:element name=''EstimateNo'' type=''xs:string'' minOccurs=''0'' /> <xs:element name=''FinalDeliveryDate'' type=''xs:string'' minOccurs=''0'' /> <xs:element name

SQL 2005 OpenXML causing deadlock

  
Gurus,
In our project we have used extensively OpenXML  statements in Stored Procedures inside SQL Transaction. Oflate, we started seeing lot of deadlocks happenning in our environment. On further investigation we found thatmoving openXML statments outside transaction block and populating the data in table variable and use that inside transaction helped us ot resolve issues. Is there anything connected with OpenXML and Deadlock. We tested with around 6-8 concurrent requestsfrom fronend app (ASP.NET)

Not found OpenXML.dll in resources !!!!

  

Hi friends,


I am creating one converter (Image to Word) in which I require DocumentFormat.OpenXML.dll file.

But I have not found it in resources.

IS there any site from where I can download this dll.

Thanks in advance.


Problem in Stored procedure when inserting data through openxml

  

I have stored proc where i insert some value from openxml but it is not inserting that xml data. Below is my stored proc.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[SP_MKTG_SERVICE_INSERT_NEW]
@ID AS INT,
@STATUS AS NVARCHAR(20),
@USERNAME AS NVARCHAR(50),
@DATEOFVISIT AS DATETIME,
@SUMMARY AS NVARCHAR(500),
@HOMESERVICE AS NVARCHAR(5),
@EXPIRYDATE AS DATETIME,
@SERVICELIST XML
AS
 SET NOCOUNT ON

BEGIN TRAN
IF EXISTS(SELECT * FROM NEW_DTL_SERVICE WHERE PARTNERID=@ID)
BEGIN
	DELETE FROM NEW_DTL_SERVICE WHERE PARTNERID=@ID
END

IF (@@ERROR <> 0)
BEGIN
	ROLLBACK TRAN
	RETURN
END
		DECLARE @IDOC INT
		EXEC SP_XML_PREPAREDOCUMENT @IDOC OUTPUT,@SERVICELIST
		INSERT INTO NEW_DTL_SERVICE
		(PARTNERID,[SERVICENAME],[TYPE],DISCOUNT,COMMISSION,CONDITION)
		SELECT @ID,SERVICENAME,TYPE,DISCOUNT,COMMISSION,CONDITION
		FROM OPENXML(@IDOC,'/NEWDATASET/TABLE1',2)
		WITH (SERVICENAME NVARCHAR(50),TYPE NVARCHAR(50),DISCOUNT NVARCHAR(50),COMMISSION NVARCHAR(50),CONDITION NVARCHAR(500))
		EXEC SP_XML_REMOVEDOCUMENT @IDOC
IF (@@ERROR <> 0)
BEGIN
	ROLLBACK TRAN
	RETURN
END
EXEC TIEDUPPARTNER @STATUS,@ID

IF (@@ERROR <> 0)
BEGIN
	ROLLBACK TRAN
	RETURN
END
COMMIT TRAN


and following is my xml which i generate to insert.

Using OPENXML in SQL Server 2008 - Unable to get the data

  

Hi,

I am trying to generate a table structure from the below XML schema:

<UserQA>
 <User ID="ABC">
  <Question ID="1">
   <Answer>A1</Answer>
  </Question>
  <Question ID="2">
   <Answer>A2</Answer>
  </Question>
  <Question ID="3">
   <Answer>A3</Answer>
  </Question>
  <Question ID="4">
   <Answer>A4</Answer>
  </Question>
  <Question ID="5">
   <Answer>A5</Answer>
  </Question>
 </User>
</UserQA>

I can able to get the values under the ekements User and questions, but i am not able to get the values under the Answers elements. If i change Answer element as Attribute specific then i can get it. but i want to use the above schema only.

Can anybody help me out.


xml node parse getting error while using openxml method

  

i'm working on importing xml data into sql using openxml method is working fine .but the data below is getting error while parse because of the single Quote or some other special character is available in <Cust_Name> that's the problem. how to solve this issue

 

<Cust_Name>John P O'Connell</Cust_Name> -- not working

<Cust_Name>John P OConnell</Cust_Name> -- working
Sudhesh. G
http://gurucoders.blogspot.com

Can I use OpenXML to extract xml string from the passed in XML?

  
Hi,

Im using OpenXML in Stored procedure to read the input XML and populate SQL tables. I have a pretty large XML which has data to be inserted into 15 tables. I want create 15 stored procedures so that each of them handles inserts into one table.

I have one main Stored procedure that gets the complete XML and from there I want to split this xml into smaller trees and pass them to the corresponding stored procedures.

In the below example, Im looking at a way to extract the XML "

<table2>

<data col1=val1 col2=val2 />

<data col1=val1 col2=val2 />

<data col1=val1 col2=val2 />

</table1>" to pass it to another stored proc.

Example:

<Data>

<col1>val1</col1>

<col2>val2</col2>

<table2>

<data col1=val1 col2=val2 />

<data col1=val1 col2=val2 />

<data col1=val1 col2=val2 />

</table1>

<table2>

<data col1=val1 col2=val2  col3=val3 col4=val4/>

<data col1=val1 col2=val2  col3=val3 col4=val4/>

<data col1=val1 col2=val2  col3=val3 col4=val4/>

<data col1=val1 col2=val2  col3=val3 col4=val4/>

</table2>

</data>

Please note that I have just mad

Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend