Questions tagged [xml]

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

Filter by
Sorted by
Tagged with
1
vote
1answer
61 views

json_modify on condition

As in my previous question, I am working on migrating my XML based procedures to JSON ones. My newest struggle is with json_modify I'm used to using .modify with XPATH to be able to modify XMLs on the ...
1
vote
1answer
55 views

Enumerating JSON arrays with OPENJSON

I've been working on converting some of my XML based stored procedures to JSON, but I'm running into an issue. With OPENXML, I had access to mp:id, so when I was parsing an XML, I could access an ...
1
vote
2answers
50 views

Execution time/cost of function in assembly

I have a query calling XmlTransform from Microsoft.MasterDataServices.DataQuality on about 1000 rows or so and it takes 30s to do the whole set (the XSL is in a fixed variable), is there any way to ...
0
votes
2answers
60 views

like in exist() xml mssql

By following question: See if XML element exists at any level in document with a specific value Is there a possible solution to implement like clause within exist? For example I have similar structure ...
0
votes
1answer
40 views

Merging two XML variables - please help!

I am using SQL Server Management Studio 17.9.1 with SQL Server 2016 Service Pack 2 (SP2). There is no table structure to show, just two XML variables that contain XML, as shown below: DECLARE @A XML = ...
0
votes
0answers
11 views

SQL Error 6304 - XML parsing: An unexpected error has occurred in the XML parser

I'm running SQL Server 2019 and not sure if it's related to this, but on SQL Server 2014 if I create a table as follows CREATE TABLE MyTable ( FieldA smallint NOT NULL, FieldB xml NOT NULL ) And ...
0
votes
0answers
16 views

Query xml multinode syntax

I have a table containing an XMLTYPE column that I have to parse and display contained values one per line: Example 1 : <c2>VLAN1</c2><c2 m="2">VLAN2</c2><c2 m=&...
0
votes
1answer
32 views

Do we have information about duration time of query in actual execution plan?

I am building application in C# which is parsing Actual Execution Plan. I need basic stats like Row count, CPU time, Page Reads etc. All above stats I found in XML of Actual Execution Plan. I can not ...
7
votes
2answers
628 views

SQL Server / XML.query() causes massive memory grant

The following (on my systems, tested on SQL Server 2016 and 2019 std) produces a massive memory grant request (3327 MB) declare @wibble xml = '<wibble><wobble>1</wobble></wibble&...
1
vote
1answer
45 views

What are the benefits of using XML over CSV files in regards to data dumps? [closed]

Prologue I've noticed that one benefit of using CSV over XML for data dumps is the fact that fewer disk space is used. See minimal working example below. Let's suppose we have the following dataset. ...
3
votes
1answer
107 views

Properly pivot an XML column with XQuery

I would like to pivot the attributes and values of an SQL XML column and display it in an ordered columns and rows format. To get started, this is what the XML column looks like in SQL: <Model ...
5
votes
1answer
610 views

Msg 6355 "Conversion of one or more characters from XML to target collation impossible" when querying sys.dm_exec_query_plan

I like to find missing indexes on the go, looking at the execution plans! It can potentially give me an indication where further to look at if I want to improve something that is currently running. ...
1
vote
3answers
241 views

How can parse column names using ordinal position (not name) from XML data in SQL Server?

I have several hundred tables in a database that have the same structure: SomeId, Pos, Varying Number of Other Fields So, for example, one table may look like this: PersonId, Pos, Hobby, Degree 12345, ...
0
votes
0answers
16 views

How should I deal with sub attributes of xml data when loading onto mysql?

I have some xml files (example below). As you can see in the xml, id 4 has 3 entries for tax. When I load it right now, only the last entry gets inserted into the table. How can I include all 3 of ...
9
votes
2answers
245 views

Why is SQL doing an index scan and seek with the same XML query structure?

I've set up a test to benchmark xml performance in SQL server. Test Setup One Million rows of data XML defined Column with primary key A primary XML index A secondary XML index on the path XML data ...
0
votes
0answers
51 views

How to open .bin SQL database?

I'm stuck and not sure if somebody had a similar issue. I have an old database stored as .bin files. It dates back to 2012. I can see an xml catalogue file which indicates to me that it is some kind ...
0
votes
1answer
55 views

sqlserver - Insert from xml generated with `select for xml`

Is there a simple way to create an XML (file, variable, value in column, whatever) with select * from table for xml ... and later automatically insert this data into identical table in different ...
1
vote
0answers
381 views

EAV vs JSON/XML column approach to store complex user form data in a database? [closed]

We are working on an internal tool which will essentially be a complex, power-user oriented form app. It is not a dynamic field app - users can't define their own fields. Form field types: The form ...
0
votes
0answers
86 views

Oracle database, convert XMLTYPE table from basicfile lob to binary xml

Trying to import an Oracle EBS database (currently in a 19c staging database) into 19c Autonomous Data Warehouse, which won't accept XMLTYPE tables with "STORE AS BASICFILE LOB", but will ...
6
votes
1answer
306 views

OPENXML to get content by using element and attribute name

In this SQL Server OPENXML example: declare @xmldata xml set @xmldata = '<?xml version="1.0"?> <wddxPacket version="1.0"> <data> <var ...
0
votes
1answer
53 views

Get the last ID from Multiple Stored Procedures Executed

So I have an XML file , to take data from it and insert into tables. For this I’ve created 3 procedures(INSERTXMLMultipleTables,INSERTXMLNIAC,INSERTNIACFORCOMPONENTS). The logic of these procedure is ...
6
votes
4answers
3k views

Error converting VARCHAR(MAX) to XML due to "UTF-8" attribute

I need to dig into a logs table with a schema similar to this: CREATE TABLE t ( id int PRIMARY KEY, data varchar(max) ); Column data stores a XML text received from a web service in this format: ...
3
votes
1answer
87 views

Mix ;WITH NAMESPACES and schema collections?

I'm getting these xml files from a vendor, and it's a wrapper of NITF (news) schema and the http://www.xmlnews.org/namespaces/meta# news metadata schema (from Space 1999!) Unfortunately, they don't ...
3
votes
1answer
100 views

Can you create a primary xml index in the CREATE TABLE statement (SQL Server)?

I'm creating a temporary table and would like to create a primary and secondary XML index on a contained xml column within the CREATE TABLE statement since that is the only way I can create an index ...
0
votes
3answers
435 views

How to allow for ampersand in XMLtable()?

Starting with this Q&A, note that while this works: with data as ( select 'Hallöle sloven&#264;ina Hallöle sloven&#273;ina' str from dual ) select a.str, b.str2 from data a, xmltable( '/...
3
votes
2answers
571 views

How to convert xml entities to unicode characters (with read-only access) in Oracle?

I'm connected to an Oracle Database (11g Release 2 - 11.2.0.4), with read-only access. Into this database, some of the data is uploaded via, or rather as, XML - and quite a few entries contain ...
3
votes
1answer
431 views

How to add Spatial Reference ID 28992 to MSSQL server?

I want to use Spatial Reference ID 28992, as found on EPSG, in MS SQL Server. How can I add this to MS SQL Server? My end goal is to get this dataset into MS SQL Server, with the Geography Markup ...
0
votes
1answer
68 views

SQL Server Deadlock OPENXML insert

I am running into a deadlock issue when I call a stored procedure in Sql Server 2014 Enterprise Edition. An XML string is passed into the stored procedure and can be called many times in quick ...
2
votes
0answers
149 views

Why does sql server consider a function that extracts a value from an xml parameter non-deterministic?

I want to add a computed field that extracts a value from another xml column. The function looks like this: create or alter function extractValue(@val xml) returns varchar(14) as begin return @val....
0
votes
1answer
95 views

does xml element exist, regardless of value?

EDIT: I demonstrated my ignorance by confusing attribute with element, I updated my question. Sorry. To clarify, I'm looking for the existence of the element code in: <conditionmessage:code>...
2
votes
1answer
187 views

How to load a table using BCP

Context: I created a dump of a sql table in XML (Including data and schema) using this command from the command prompt. BCP "SELECT * FROM Database.Schema.TableName FOR XML AUTO, XMLSCHEMA" ...
0
votes
1answer
40 views

How extract cldr timezones from xml file to store in a DB table? [closed]

In our DB, I need each location to be associated with a "timezone". These timezones are in https://github.com/unicode-org/cldr/blob/release-36-1/common/supplemental/metaZones.xml I want to make a ...
0
votes
1answer
2k views

XQuery in SQL Server to convert XML column data to relational data table

I previously asked a question about an error I was recieving. You don't really need it to understand this question but it's here for reference: XML/SQL Server 2008 Error: XQuery...Cannot implicitly ...
0
votes
1answer
262 views

Spooling Oracle XML data results in 1 line and missing data?

I am trying to spool Oracle XML data to a file but the resulting file ends up with 1 line which is stops after a certain amount of characters so doesn't contain all of the data? set colsep , set ...
0
votes
1answer
93 views

vertica/postgres copy text to binary column

Until now I used Oracle to store xml file in blob data type column. Is there any option in Vertica to copy xml file into binary column? I saw there is varbinary data type but, I haven't succeeded to ...
0
votes
2answers
26 views

Get one row for each value in xml column

I have a table, called product_variants, with one integer column and one xml column containing a set of foreign key values. If I ran, select product_id, options_xml from product_variants This would ...
1
vote
1answer
32 views

Querying XML Nodes with SQL

I think I'm nearly there with this but have reached an impasse! I've a relatively straightforward XML structure that I need to parse out as a SQL resultset but can't seem to get it to work. Here is ...
2
votes
1answer
998 views

Postgresql query XMLTable with default namespace

I use PostgreSQL 12 to query XML data. My data is as below CREATE TABLE xmldata AS SELECT xml $$ <ROWS xmlns="http://x.y.z"> <ROW id="1"> <COUNTRY_ID>AU</COUNTRY_ID> ...
9
votes
2answers
531 views

Xml index, slow count of rows

I am not getting index seek in the following scenario. Instead of inserting <Number>xxx</Number> in xml column like in this post Why is the secondary selective index not used when the ...
1
vote
1answer
72 views

If XML child element value contains string return other child element value at same level

I have a database table named Warehouses that contains rows of data related to specific warehouses. One column in that table is named itemStats and contains XML data in the following format: <...
1
vote
1answer
673 views

Extract Information from XML Document Attributes into PostgreSQL

I am fairly new to SQL, and very unfamiliar with XML. I want to extract the key data from the XML document below into PostgreSQL, but I am having difficulties. Basically, for every data series, I ...
0
votes
1answer
628 views

How to loop through all table rows and perform subquery using values of same row?

I have a table structured as follows: | reference | valA | valB | xml | 1 NULL NULL <xml stuff> 3 NULL NULL <xml stuff> 4 NULL NULL ...
1
vote
1answer
779 views

ERROR: invalid XML content, when Select unnest(xpath(... on a text column

I have a text column named big_xml in my table which has the following value: <?xml version=1.0 encoding=UTF-8?> <dpid:DpidDatabase xmlns:dpid=http://ddex.net/xml/dpid/11 xmlns:xsi=http://...
4
votes
2answers
427 views

Search for values in xml data

I have a table of names: Table A John Jim Jason and a table of xml strings: Table B Example 1 <Show Title="" ShowTitle="=False" ShowLine="=False" ShowDescription="=False" ...
0
votes
1answer
438 views

How to Load XML child nodes in to multiple tables

I would like to load the below XML into 2 tables Table 1: Acct Table - it should have the ACCT_NUM and a Key to tie to the Table 2 below Table 2: Should have all the Contacts (1 record per ...
0
votes
1answer
43 views

XQquery for specific attribute of all specific elements in XML

I am, looking for a query to get all attribute values "NetAmount" of Element "PRODUCT" from the following samle XML: <ROOT> <CATEGORY name="A"> <PRODUCT NetAmount="10.50"/> <...
8
votes
3answers
197 views

XML Query Question

I'm trying to generate a SQL query to get the "DATE" values out of the following SQL XML: I've tried something like this but I don't think I'm understanding the concepts. select xConfig.value('(/...
3
votes
1answer
128 views

Why is an empty XML node xml datatype not being recognized in this query?

My query seems to not recognize self-closing or empty nodes at all. Shouldn't the empty <td/> node return 'null'? Or an empty string. Even if I place an empty space in the node, it still is ...
7
votes
2answers
235 views

XQuery doesn't update XML data

I am trying to run the script to update an XML column: UPDATE DataImpTable SET serviceData.modify('replace value of (/SMObjInfo/CentralData/SMData/CentralSDItem/ControlData/text())[1] with "9876"') ...
14
votes
3answers
4k views

Concatenate all values of the same XML element using XPath/XQuery

I have an XML value like this: <R> <I>A</I> <I>B</I> <I>C</I> ... </R> I want to concatenate all I values and return them as a single string: ...

1
2 3 4 5
8