Short for 'eXtensible Markup Language.' A textual markup language for transferring structured data.

learn more… | top users | synonyms (1)

8
votes
1answer
397 views

Replace value of strongly typed xml element in SQL Server with XQuery

Given a element, defined within an XML Schema Collection as such: <xsd:element name="xid"> <xsd:simpleType> <xsd:restriction base="xsd:string"> ...
6
votes
1answer
79 views

Why does assigning FOR XML to an XML variable warn about CONVERT_IMPLICIT(XML)?

SET STATISTICS XML ON; DECLARE @x XML = (SELECT 'hi' "@id" FOR XML PATH ('this'), root ('xml')); Type conversion in expression (CONVERT_IMPLICIT(xml,[Expr1001],0)) may affect ...
3
votes
1answer
143 views

Function Based Index with XPATH extractValue

// Oracle Version 11.2.0.4 Standard Edition // OS RedHat I am trying to create a function index on a large table, based on using an XML object. So I have done stuff such as: CREATE INDEX ...
1
vote
1answer
143 views

MySQL - include a default value in LOAD XML INFILE statement

I have the follwing table: CREATE TABLE `tab1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `DEFAULT` VARCHAR(25) DEFAULT NULL, `ITEM` varchar(25) DEFAULT NULL, `TIMESTAMP` TIMESTAMP ...
2
votes
1answer
23 views

Returning XML Value for Extended Event

I would like to get the filepath for my extended event event_file and put it inside a variable but I'm stuck. Here's what I have: DECLARE @ExpensiveXML XML SET @ExpensiveXML = (SELECT ...
3
votes
1answer
94 views

How to call modify() on a nodelist in T-SQL?

I have a process step where I have an XML document in a SQL Server column and I need to alter the text of a node that could occur multiple times. Modify() with 'replace' DML only operates on single ...
0
votes
2answers
52 views

Should I use XML for data storage on client side?

I have to create a supplementary program that runs offline on a client machine (normally windows 7 or higher laptop). Of course, I don't want to create a pre-requisite for this program. Since this ...
0
votes
1answer
132 views

Database structure for object with dynamic attributes

I am developing a proof of concept for a personalized email sending web application (like MailChimp, but with premade dynamic templates). The general structure is that I have my templates (different ...
0
votes
3answers
509 views

SSIS - Best way to insert large number of rows

So here's the scenario: I have an XML file, which is in size of 500GB, and with data of around 600 million rows (once on a database table). I'm using SSIS for the operation, and since it consumes a ...
1
vote
1answer
631 views

How to update/insert/inject nodes into XML

I have an application which stores optional Site information in an XML field. Sample table: CREATE TABLE [dbo].[Sites]( [SiteID] [int] IDENTITY(1,1) NOT NULL, [SiteName] [nvarchar](80) ...
6
votes
2answers
2k views

FOR XML could not serialize the data because it contains character (0x0000)

I have a big query (if necessary I will post it here) and I'm getting this error: Msg 6841, Level 16, State 1, Line 1 FOR XML could not serialize the data for node 'NoName' because it contains ...
5
votes
2answers
9k views

How do I import XML to a SQL Server table?

Can anyone help me work towards fixing my solution to import my XML data into a SQL Server Table? I have done my research, but this task has been very hard for me to accomplish. Everything I have ...
1
vote
1answer
236 views

sql org chart hierarchical table to xml json

I have a table of employees and managers: create table emp_mgr (emp varchar(99), mgr varchar(99)) insert into emp_mgr select 'Adam',NULL; insert into emp_mgr select 'Bob','Adam'; insert into emp_mgr ...
3
votes
2answers
55 views

Shreded system_health ExEvent not returning all node information

SQL Server is not returning all node information when I shred the deadlock XML from the 2012 system_health Extended event. When I run the following query SELECT ...
10
votes
1answer
181 views

Why is the secondary selective index not used when the where clause filters on `value()`?

Setup: create table dbo.T ( ID int identity primary key, XMLDoc xml not null ); insert into dbo.T(XMLDoc) select ( select N.Number for xml path(''), type ) from ( ...
1
vote
1answer
149 views

Querying XML with Unicode data

I'm trying to select attribute value from XML column using XPath with a parameter that can be a Unicode string. Could you please tell me how to make these two examples work? create table #test ( ...
1
vote
2answers
623 views

Collation conflict when running dynamic sql FOR XML PATH

I am getting the following error message when running the query below: Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_BIN" in the equal to operation ...
1
vote
1answer
83 views

How do I use a SP in SQL Server 2014 to call a external XML source?

I am attempting to retrieve Currency Exchange rates from http://www.floatrates.com/daily/usd.xml using a SP in SQL Server 2014. The result is in XML. So I created the following SProc: --DROP ...
3
votes
2answers
168 views

XML vs. Relational databases?

I feel like this isn't the best question to ask on a programming site like this, but I haven't really found a satisfying answer online and I would really like to get a different perspective on this. ...
0
votes
2answers
265 views

xml parameter to distributed query - difference between SQL2005 & SQL2012

I have inherited an old SQL2005 Enterprise database (on Windows 2003) that allows an xml parameter to a distributed query such as this: exec sp_executesql 'exec [server].[database].dbo.sproc ...
0
votes
1answer
30 views

Can SSRS XML data sources contain spatial data?

I've been given the task of producing a report which will extract data from a web service. The web service provides data from a list of sites including aggregates. Lets say total sales by month. ...
1
vote
3answers
167 views

Load specific element from XML

I have the following xml: <root> <123 type="line"> <aa type="item"> <value>1</value> </aa> </123> <234 type="line"> <bb type="item"> ...
2
votes
1answer
127 views

search xml column inside large table (7 million rows)

Database design: Table Name : Plays ( 6 million records) Columns: Play id, col1, col2......col7, PlayFieldValues PlayFieldValues xml column. sample xml stored in this column <PlayAttributes> ...
2
votes
2answers
4k views

sp_send_dbmail stored procedure send with attachment

I've been tasked with sending a small monthly report to for one of my customers. The report has previously been run manually on the instance, the output copied to a spreadsheet and send to the ...
1
vote
0answers
54 views

Create Nested XML in a Sql Server Indexed (Materialized) View

This page https://msdn.microsoft.com/en-us/library/ms188276.aspx indicates that if you wanted nested XML (ie an XML Tree) then you need to setup your query like this: SELECT Col1, Col2, ...
1
vote
1answer
113 views

How do I optimise XPath Querying over a large dataset?

I have a table that has 220 M rows. Also the table has a xml column that has XML data. I have configured single file for data and another ndf for index. Table's primary key is guid column. When am ...
1
vote
1answer
240 views

SQL Server to XML files alternatives

I am currently looking for an alternative to writing a large, complex XML file based on a known specification. Currently the file is written in t-sql as a stored procedure. The output is in a results ...
3
votes
1answer
398 views

Can I split lines into Elements with For XML?

In this example I'm cribbing from a previous question, My source table has a varchar(max) field that I want to split by line into individual elements with FOR XML as I query it. Given this data- ...
1
vote
1answer
101 views

how to output 3 column into 3 element in xml format with the same name?

I have this code and I can't figure out how to get the expected result? declare @table table (TAG int, PARENT int,col1 varchar(100) ,col2 varchar(100) ...
1
vote
2answers
1k views

Improve performance of converting a large xml file (~300 MB) to relational table in SQL Server

So this is what I have so far: --Read xml content into a XML data type variable DECLARE @FileData XML SELECT @FileData = CONVERT(XML, BulkColumn) FROM OPENROWSET(BULK '\\file_path\test.xml', ...
1
vote
1answer
1k views

HTML entity decoding with oracle DBMS_XMLGEN.convert

Here, I am trying to convert the value, where I don't understand I am going wrong. Your help is highly appreciated! Here I am#44; THis is me there#39;s nowhere else on earth I rather be Here I am#44; ...
6
votes
1answer
2k views

How to avoid using Merge query when upserting multiple data using xml parameter?

I am trying to update a table with an array of values. Each item in the array contains information that matches a row in a table in the SQL db. If the row already exists in the table, we update that ...
2
votes
1answer
3k views

How to update a table with a stored procedure with XML parameter in SQL 2008?

I have the following Stored Procedure query. This query is incomplete. I need help completing this query. Problem: In this SP, I am receiving a well-formed XML string into the SP. My goal is to ...
1
vote
0answers
253 views

export data to xml by the use of query to xml then importing it using xpath

i have use QUERY_TO_XML function in order to export data to xml , after that i want to export this data. this data might contain reserved chars in the xml tags like "' & ..." ex electrical ' ...
4
votes
3answers
2k views

Help with SQL Query to XML output

I have the following query: SELECT [ID] AS [NewID] , ( CASE WHEN LEN([MiddleName]) > 0 THEN [LastName] + ', ' + [FirstName] + ' ' + LEFT([MiddleName],1) + '.' ...
4
votes
1answer
2k views

for xml missing null columns

When we use for xml clause to create xml from a row like as follows select * from Menus where MenuID=100 for xml path('') The xml will miss the columns which have NULL as the value. I have a found ...
4
votes
2answers
1k views

SQL Server 2008R2 XML Column Index strategy for large tables?

I am working with SQL Server 2008 R2 and some XML columns in a very large table. I understand that I cannot use Selective XML Indexes due to our SQL Server version being < 2012. I'm brand spanking ...
0
votes
1answer
367 views

XMLTABLE solution without getting right parenthesis missing error

I am trying to select from an XML type and produce multiple rows. Have searched for my specific scenario/issue with not much success. Environment: Oracle 12c. I have commented out the offending code. ...
1
vote
1answer
436 views

XMLDB - Nodes to Rows

I have an application that stores XML as XMLType in a table. This is the first time I'm using XMLDB and XMLType features, and was wondering if I could use the results from an XQuery FLWOR expression ...
2
votes
2answers
1k views

How to convert html entities in Oracle?

I'm trying to decode html containing html entities. I've tried dbms_xmlgen.convert and utl_i18n.unescape_reference but the results are less than satisfactory. SET ESCAPE ON; SELECT ...
0
votes
1answer
483 views

PostgreSQL import XML to empty database, automatically create columns

The server is Linux but the development machine is Windows so I'm using psql command line. I tried \i C:/Users/John/Desktop/Parts.xml which did not work so either \i doesn't work with XML or I'm ...
0
votes
1answer
236 views

Is there a more efficient way to apply a new schema to an existing collection when there's existing data?

I'm needing to add a new schema to the schema collection on an existing table. Right now, I simply unbind the old schema collection from the table column, drop it, create the new schema, and rebind it ...
1
vote
3answers
139 views

Longest matching suffix while using aggregate functions

Background Looking to find the longest matching string suffix. Setup Consider the following fiddle: CREATE TABLE noun ("label" varchar(10)) ; INSERT INTO noun ("label") VALUES ...
-4
votes
1answer
277 views

xml values are not inserting into the table [closed]

Here is my code: ALTER PROCEDURE sp_Insertxmldata @xml xml AS BEGIN declare @hdoc as int EXEC sp_xml_preparedocument @hdoc output,@xml Insert into ...
2
votes
1answer
333 views

XML Whitespaces/Unwanted Characters [closed]

Could anyone help on how to remove white spaces from an XML file? I used the following code that i got from Mikael Eriksson on Stack Exchange to extract the site id: cast(msg as ...
1
vote
3answers
3k views

Extracting an an integer value from an xml column [closed]

I'm just wondering how I can extract an integer out of an XML column. Currently all the digits are 4 which the below query is able to do. But the number is going to grow to 10,000's soon which will ...
2
votes
2answers
455 views

Possible to optimize SELECT query involving XQuery functions selecting from elements with arbitrary number of child elements

Suppose I have the following XML data, where <root> can have an arbitrary number of <child> children <child> can have an arbitrary number of <grandchild> children ...
3
votes
0answers
456 views

Ways to process XML into a relational structure in Postgres

I have a database that is comprised of ~10 tables, most of which have many-to-many relationships with each other (and an associated join table). I am inserting of the order 100k XML files into a raw ...
2
votes
1answer
16k views

Simplest way to edit single entry in XML column?

As I understand MS SQL Server Management Studio doesn't allow directly editing/replacing entries in XML columns via GUI (copy/paste doesn't work etc.). What is the easiest option to replace a single ...
5
votes
1answer
3k views

Can I use attribute name to identify XML element?

Rather than using the element index COLUMN[1]/@value below, is it possible to retrieve the column value by specifying an attribute name such as: COLUMN[@name=”Prod_ID”]/@value? When I tried, I got ...