Questions tagged [sql-server-openxml]

OPENXML is a SQL Server keyword that provides a rowset over in-memory XML documents which can be treated like a table or a view.

OPENXML, a Transact-SQL keyword, provides a rowset over in-memory XML documents that is similar to a table or a view. OPENXML allows access to XML data as though it is a relational rowset. It does this by providing a rowset view of the internal representation of an XML document. The records in the rowset can be stored in database tables.

OPENXML can be used in SELECT and SELECT INTO statements wherever rowset providers, a view, or OPENROWSET can appear as the source. For information about the syntax , see OPENXML (Transact-SQL).

To write queries against an XML document by using OPENXML, you must first call sp_xml_preparedocument. This parses the XML document and returns a handle to the parsed document that is ready for consumption. The parsed document is a document object model (DOM) tree representation of various nodes in the XML document. The document handle is passed to OPENXML, which then provides a rowset view of the document based on the parameters passed to it.

Source: Microsoft (OPENXML Keyword)

55 questions
1
vote
2 answers

Reading XML attribute values using OPENXML

For the following .nodes() approach, I need an equivalent OPENXML approach. The Attributes will be different and can not be hard-coded. DECLARE @Xml XML='
RandomUser
  • 1,843
  • 8
  • 33
  • 65
1
vote
3 answers

Import Excel Data Into Temporary Table Without Using OLEDB in SQL Server

I am trying to look for a way to import excel data into a temporary table without using OLEDB since I don't have OLEDB installed in SQL Server and I can not install it due to security restrictions. I am aware of below mentioned ways of doing it but…
Omi
  • 427
  • 7
  • 21
  • 42
1
vote
2 answers

Parsing XML file with inline schema using OPENXML in SQL Server

I am trying to extract data from XML file and store in to SQL table using OPENXML in SQL Server. But, the query returns nothing. XML data
user10987050
  • 75
  • 10
1
vote
1 answer

SSIS - export to excel through open XML

Earlier we have used Microsoft OLEDB JetProvider in SSIS package. After recent update from Microsoft, now we are facing issues with SSIS package. So we have decide to export data to excel using open XML. What should be the best approach for…
Tech Learner
  • 1,227
  • 6
  • 24
  • 59
1
vote
1 answer

The multi-part identifier could not be bound.[4104]

I'm updating a table through XML file but when I execute the sql code it throw this error :- And here is my code IF(@PreppedUpdateModelXml is NULL OR @PreppedUpdateModelXml.exist('*') = 0) BEGIN --Create an internal…
Abhay
  • 47
  • 1
  • 10
1
vote
2 answers

SQL Server query XML datatype performance issue

I have a XML file stored in a XML datatype column data in my table records. The table looks like this: create table records ( id int, type nvarchar(28), data xml, posted datetime ) XML data:
OmGanesh
  • 952
  • 1
  • 12
  • 24
1
vote
1 answer

Get rows from XML array using OPENXML

I have following XML: Some Name Another Name As the result I would like to have: Some Name Another Name To achieve that I am trying to execute following query: DECLARE…
1
vote
2 answers

SELECT * FROM OPENXML IN STORED PROCEDURE returns empty row

I have a problem using openxml function in my stored procedure, as you can see, the select statement returns empty row. I suspect the sql couldn't read the xml directories.this is the first time i'm using this function, so i am not sure is this the…
1
vote
1 answer

Select certain repeating XML node by name at various levels & repeating sub-nodes as rows from within said node

Take the following simplified XML: (An excerpt from a very expensive (and large) XML product feed from an 3rd party industry wholesaler data provider - i.e. I have no control over its schema / format / content) 7786
HeavenCore
  • 7,533
  • 6
  • 47
  • 62
1
vote
2 answers

OpenXML Import XML file to SQL server table

I am trying to set up an SQL job to import an XML file into an SQL Server table. Using OPENXML, I can't seem to select the specific data I need from the file. Here's my code and XML data. I am trying to select Facility and Entity_Code but when I run…
user1781272
  • 862
  • 2
  • 14
  • 25
1
vote
1 answer

SQL Inserting data in master table and then reference data in detail table

I have two tables: MasterReg (MasterID, Revenue, Date, Desc); MasterID is an Identity column DetailReg (DetailID, MasterID, NumValue); DetailID is an Identity column I am trying to insert data from a xml string using openxml insert into…
Neo Jekson
  • 21
  • 5
1
vote
1 answer
1
vote
3 answers

Call stored procedure for each XML element without cursors

I have this stored procedure in SQL Server: alter PROCEDURE [dbo].[spSendLogLinesAsXML] ( @device_id varchar(128), @application_name VARCHAR(64), @application_user_name VARCHAR(6), @log_lines_xml XML ) AS BEGIN DECLARE @ixml…
cja
  • 9,512
  • 21
  • 75
  • 129
1
vote
1 answer

How to save OpenXML Word.docx in SQL for free text search (C#)

Could please anyone help me to save my Word.docx (OpenXML) document in SQL Server (C#). In my scenario I have to do the free text search on that document in SQL. I have made my sql table indexable but dont know how to insert that OpenXML document in…
Learning Curve
  • 1,449
  • 7
  • 30
  • 60
0
votes
2 answers

T-SQL - OPENXML

I have the following SQL code: DECLARE @DocHandle int; DECLARE @XmlDocument nvarchar(4000); SET @XmlDocument = N' 154681546915472
Mark
  • 4,535
  • 7
  • 39
  • 76