I’m having a few issues when inserting data from a XML file to a SQL Server database. How can I get the file name dynamically? This file is put on that path with a different number every time but always starts with Idoc and it’s a .xml file. I’m having troubles setting a variable inside OPENROWSET. At the moment I’m
Tag: xml
Filter an XML column in SQL Server
I have an xml type column productsXML in product table I want to find all the rows that have <products></products>. I tried this: This is returning all the rows that have products tag and understandably so. Is there a way to filter only those rows that have <products></products>? Answer PLease try the following solution. It is checking that the root
How to extract attribute value from XML in SQL Server 2019 (v15)?
I would need to extract elements from this XML into a tabular form, but I can’t seem to get my head around how this would work on SQL Server via something like XQuery. I have all the data in a temporary table called “#1” and the XML itself lies in a field called “Message” in that temporary table. How can
Xquery Get Consecutive Distinct Values
Trying to get consecutive distinct from the following XML. Expected Result : Current Result : Code : The distinct-values get distinct from the whole set, but I would like to get distinct values only if there are consecutive. Answer We have a pure XQuery solution. SQL Output
Fetch data from XML – XMLGet function using with Attribute Name and its value in Snowflake
I have an xml which I need to read using Snowflake SQL. I need to use the attribute name and lits value to fetch the data. For example -> id and “bk101” to fetch the contents. I am currently using below query- In this case I am passing the index 0-4 to fetch the data is not producing correct results.
How to use Oracle XMLTable to retrieve the value/condition inside a tag?
I have a SQL using XMLTable to retrieve tag values such as Tag>Value</Tag which works fine. But what about value/condition inside the tag? See following XML with tag T2 having a remove condition = true. How can I retrieve that condition using XMLTable? Answer Pretty much the same way, just use the XPath syntax to match attributes. Leaving out namespaces
Delete node when child have a certain value is found
I intend to remove a node whenever I find the unit_qty tag with the value equal to 0.0000. For example considering that the message below is called MESSAGE_DATA: the purpose of message_data is to return the following: I’ve already tried the following options: I don’t know if I’m looking up the tag wrong but I’ve tried it with several formats
XMLTable query returns no result
I’m only passingly familiar with XML. I need to parse a response from a SOAP request. From a lot of searching, I’ve developed the following query to try to extract the status. Ultimately, I’d like to get the status, cntr and cntr_status fields from the response. My query gives no error, but also no results. What noob error am I
Insert repeating tag xml data to multiple sql table columns
I’m having following XML and query. header tags are there and Item tag can be repeated up to 10 tags. I want to insert those item no and qty in separate columns. Here I mentioned only 2 tags for Item but in real situation there are 5 tags in XML. So in worst case scenario it will be 50 columns.
how to read a XML string value with leading space in sql server
I want to store the value of userId as ” XYZ” but when after executing the below code got output as “XYZ”. Want to store the value with leading space. Answer You may try to parse the input XML using a variable of XML data type and a combination of nodes() and value() methods: Using this approach you may parse