Menu
  • HOME
  • TAGS

how to make xml values comma seperated using XPath, XQuery in Sql Server

Tag: sql-server,xml,xpath,xquery

I have an xml column with sample values as

<error>
  <errorno>BL04002055</errorno>
  <description>Smart Rule PROJECT_COUNTRYCODE_VAL Violated</description>
  <description2>Country Code is required</description2>
  <correction />
</error>
<error>
  <errorno>BL01001973</errorno>
  <description />
  <description2>Error While Saving the Project info</description2>
  <correction />
</error>
<error>
  <errorno>Unable to Create Custom Object</errorno>
  <description />
  <description2>Smart Rule PROJECT_COUNTRYCODE_VAL Violated: Country Code is required
Error While Saving the Project info</description2>
  <correction />
</error>

I want to select description2 values comma seperated

select *
--, Response.value(''/error/description2/text()'', 'varchar(8000)') as parsedString
, Response.query('/error/description2/text()') as parsedString
 from #temp

Two problems here.

  1. I am not able to run value function in query above.
  2. Using query, I get values concatenated without space or comma. So I need to add some space or comma on concatenated values.

Best How To :

SQL Server does not implement the xPath function string-join, so you would need to adopt a two step process, the first would be to extract all the terms to rows using nodes();

SELECT  n.value('.', 'VARCHAR(100)') AS parsedString
FROM    #temp AS t
        CROSS APPLY t.Response.nodes('/error/description2') r (n);

Which gives you your values as rows:

parsedString
----------------------------------------------------------------------------
Country Code is required
Error While Saving the Project info
Smart Rule PROJECT_COUNTRYCODE_VAL Violated: Country Code is required
Error While Saving the Project

Then you can add your delimeter and concatenate them back up, using FOR XML PATH(''), TYPE, and finally use STUFF to remove the first delimeter:

SELECT  STUFF(( SELECT  ',' + n.value('.', 'VARCHAR(100)') AS parsedString
                FROM    #temp AS t
                        CROSS APPLY t.Response.nodes('/error/description2') r (n)
                FOR XML PATH(''), TYPE
            ).value('.', 'VARCHAR(MAX)'), 1, 1, '') AS ParsedString;

FULL WORKING EXAMPLE

DECLARE @X XML = '<error>
  <errorno>BL04002055</errorno>
  <description>Smart Rule PROJECT_COUNTRYCODE_VAL Violated</description>
  <description2>Country Code is required</description2>
  <correction />
</error>
<error>
  <errorno>BL01001973</errorno>
  <description />
  <description2>Error While Saving the Project info</description2>
  <correction />
</error>
<error>
  <errorno>Unable to Create Custom Object</errorno>
  <description />
  <description2>Smart Rule PROJECT_COUNTRYCODE_VAL Violated: Country Code is required
Error While Saving the Project info</description2>
  <correction />
</error>';

SELECT  STUFF(( SELECT  ',' + n.value('.', 'VARCHAR(100)') AS parsedString
                FROM    (SELECT @X) AS t (Response)
                        CROSS APPLY t.Response.nodes('/error/description2') r (n)
                FOR XML PATH(''), TYPE
            ).value('.', 'VARCHAR(MAX)'), 1, 1, '') AS ParsedString;

Ruby- get a xml node value

ruby,xml

Try to use css instead of xpath, this will work for you, doc = Nokogiri::XML(response.body) values = doc.css('Name').select{|name| name.text}.join',' puts values => Ram,Sam ...

Recursive Lag Column Calculation in SQL

sql,sql-server,recursion

Edit In hindsight, this problem is a running partitioned maximum over Column1 * 2. It can be done as simply as SELECT Id, Column1, Model, Product, MAX(Column1 * 2) OVER (Partition BY Model, Product Order BY ID ASC) AS Column2 FROM Table1; Fiddle Original Answer Here's a way to do...

List view not returning to original state after clearing search

java,android,xml,android-activity,android-listfragment

You are operating on the original data instead of filtered data. You should maintain a reference to original data and use the filtered data for all other purposes. So that the original data is displayed when search is cleared. Replace all usages of mData with mFilteredData as below and only...

TSQL - Error in stored procedure due to conversion failure

sql-server,sql-server-2008,tsql

I'd suggest doing it like that: SET @DATE_RELEASE_START = '2015-01-01'; SET @DATE_RELEASE_END = '2015-05-31' SELECT @statement = ' SELECT * FROM (SELECT AFCDENTE, M.ID_MODIFICATION_CODE, COUNT(*) AS Conteggio--, CAST((COUNT(*) * 100/ 15032) AS decimal(10,7)) AS Percentage FROM CIC_LOG_MODIFICHE AS L INNER JOIN ADM_MODIFICATION_CODE AS M ON L.CD_MODIFICATION_CODE = M.CD_MODIFICATION_CODE INNER JOIN...

Get unique row by single column where duplicates exist

sql,sql-server

SELECT MIN(date),thread_id FROM messages GROUP BY thread_id HAVING COUNT(thread_id) > 1 ...

Merging two tables into new table by ID and date

sql,sql-server,phpmyadmin

You can use a SELECT statement when inserting into a table. What I would do here is write a select statement that pulls all of the columns you need first. You will have to do a full outer join (simulated by a union of left and right joins) because some...

Setting time limit in SQL Query

sql-server

Assuming that you want between 10:00 AM and 5:00 PM, you can use this SELECT CASE WHEN CAST(GETDATE() AS TIME) BETWEEN '10:00:00' AND '17:00:00' THEN 1 ELSE 0 END In this context, Select * from table makes no sense, unless you have a time column and want to evaluate that....

Implement reference key in SQL Server

sql-server,sql-server-2008

It's called a "one-to-zero-or-one" relationship, as one Line might be associated to zero or one TestPacks. You can implement it by using a FK that allows NULL values. CREATE TABLE TestPack (id INT, PRIMARY KEY (id)) CREATE TABLE Line (id INT, TestPackId INT NULL, FOREIGN KEY (TestPackId) REFERENCES TestPack(id)) By...

Retrieve data from one table and insert into another table

sql,asp.net,sql-server

INSERT INTO tbl2 ( Name ,parentId ) SELECT DISTINCT manager ,0 FROM tbl1 WHERE manager NOT IN ( SELECT employee FROM tbl1 ) INSERT INTO tbl2 SELECT DISTINCT employee ,0 FROM tbl1 UPDATE tbl2 SET parentid = parent.id FROM tbl2 INNER JOIN tbl1 ON tbl2.Name = tbl1.employee INNER JOIN tbl2...

Error connecting to MSSQL using PHP

php,sql-server,pdo,odbc,sqlsrv

Change it to: $this->link = new PDO( "sqlsrv:Server={$this->serverName},{$this->port};Database={$this->db};", $this->uid, $this->pwd ); The default SQL Server port is 1433. Note the curly brackets, they allow for class variables....

Default the year based on month value

sql,sql-server

SQL Server is correct in what it's doing as you are requesting an additional row to be returned which if ran now 2015-06-22 would return "2016" Your distinct only works on the first select you've done so these are your options: 1) Use cte's with distincts with subq1 (syear, eyear,...

Title search in SQL With replacement of noice words [on hold]

sql,sql-server,sql-server-2008

I think you want something like this: DECLARE @nw TABLE ( sn INT, [key] VARCHAR(100) ) INSERT INTO @nw VALUES ( 1, 'and' ), ( 2, 'on' ), ( 3, 'of' ), ( 4, 'the' ), ( 5, 'view' ) DECLARE @s VARCHAR(100) = 'view This of is the Man';...

Take thousand value in SQL

sql,sql-server

SELECT CONVERT(INT,YourColumn) % 1000 FROM dbo.YourTable ...

Join SQL query Results and Get-ChildItem Results

sql-server,sql-server-2008,powershell

OK so if the SQL query does not have results then NULL is returned and, in essence, nothing is added to the $dbResults array. Instead lets append the results to a custom object. I don't know what PowerShell version you have so I needed to do something that I know...

odoo v8 - Field(s) `arch` failed against a constraint: Invalid view definition

python,xml,view,odoo,add-on

You have made silly mistake in defining _columns. _colums is not valid dictionary name for fields structure. Replace this by _columns and restart service and update module. ...

Select count Columns group by No

sql,sql-server

this will work. you have to provide separate case statement to each condition SQLFIDDLE for the same SQLFIDDLE SELECT EMP_NO, sum(CASE WHEN Emp_Shift = 'AL' THEN 1 ELSE 0 END) AS COUNT_AL, sum(CASE WHEN Emp_Shift = 'S' THEN 1 ELSE 0 END) AS COUNT_S, sum(CASE WHEN Emp_Shift = 'H' THEN...

About sorting based on the counting of subelements

xml,xslt

You can use a key in order to count the properties in the sort instruction. A stylesheet containing the following: <xsl:key name="p" match="property" use="@agency"/> <xsl:template match="/immo"> <result> <xsl:for-each select="agency"> <xsl:sort select="count(key('p', @name))"/> <res id="{ @name }" count="{ count(key('p', @name)) }"/> </xsl:for-each> </result> </xsl:template> when applied to the following input: <immo>...

C# XML: System.InvalidOperationException

c#,xml

Is "User Info" and "Course Data" is a different entity. If it is so, I think you may encapsulate them in one entity. XmlTextWriter writer = new XmlTextWriter(path, System.Text.Encoding.UTF8); writer.WriteStartDocument(true); writer.Formatting = Formatting.Indented; writer.Indentation = 4; writer.WriteStartElement("My Entity"); /* It is a biggest one*/ writer.WriteStartElement("User Info"); writer.WriteStartElement("Name"); writer.WriteString(userName); writer.WriteEndElement(); writer.WriteStartElement("Tutor...

XElement.Value is stripping XML tags from content

c#,.net,xml,xml-parsing,xelement

As others have said, this format is truly horrible, and will break as soon as the XML embedded in the JSON will contain double quotes (because in the JSON they will be encoded as \", which will make the XML invalid). The JSON should really be embedded as CDATA. Now,...

Error when building an XDocument

c#,xml,linq,xpath,linq-to-xml

You can ignore pretty much all your code, the issue is just this: XDocument people = new XDocument("Persons"); You can't create an XDocument containing a string, you need to add an element: XDocument people = new XDocument( new XElement("Persons", original.XPathSelectElements(xpathFilterDups))); ...

Fastest way to add a grouping column which divides the result per 4 rows

sql,sql-server,tsql,sql-server-2012

Try this: SELECT col, (ROW_NUMBER() OVER (ORDER BY col) - 1) / 4 + 1 AS grp FROM mytable grp is equal to 1 for the first four rows, equal to 2 for the next four, equal to 3 for the next four, etc. Demo here Alternatively, the following can...

SQL Multiple LIKE Statements

sql,sql-server,tsql,variables,like

WITH CTE AS ( SELECT VALUE FROM ( VALUES ('B79'), ('BB1'), ('BB10'), ('BB11'), ('BB12'), ('BB18'), ('BB2'), ('BB3'), ('BB4'), ('BB5'), ('BB6'), ('BB8'), ('BB9'), ('BB94'), ('BD1'), ('BD10'), ('BD11'), ('BD12'), ('BD13'), ('BD14'), ('BD15'), ('BD16'), ('BD17'), ('BD18'), ('BD19'), ('BD2'), ('BD20'), ('BD21'), ('BD22'), ('BD3'), ('BD4'), ('BD5'), ('BD6') ) V(VALUE) ) SELECT * FROM tbl_ClientFile...

Parsing XML array using Jquery

javascript,jquery,xml,jquery-mobile

EMI and CustomerName are elements under json so you can use .find() to find those elements and then text() to get its value. $(data).find("json").each(function (i, item) { var heures = $(item).find("CustomerName").text(); var nbr = $(item).find("EMI").text(); console.log(heures); }); .attr() is used to get the attribute value of an element like in...

ONLY display certain rows from an inner joined table using a certain colum as a parameter from one of the inner joined tables

sql-server,join

If I understood correctly this should be what you're looking for SELECT A.UserName, A.[Email ID], A.[Supervisor Email ID] FROM A INNER JOIN B ON A.UserCode = B.UserCode WHERE B.ACTIVE_FLAG = 'Y' ...

Load XML to list using LINQ [duplicate]

c#,xml,linq

Make a base class which will have id,x,y,z, and have Vendors,Bankers and Hospitals extend it. Then you can have a collection of the base class, and add to it the classes that inherit from it....

The column name “FirstName” specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument

sql,sql-server,sql-server-2008

You could use CTE to define your null values and then pivot the data something like this: ;WITH t AS ( SELECT isnull(jan, 0) AS jan ,isnull(feb, 0) AS feb ,sum(data) AS amount FROM your_table --change this to match your table name GROUP BY jan,feb ) SELECT * FROM (...

How to calculate max string-length of a node-set?

xml,xslt,xslt-1.0,libxslt

<xsl:variable name="max_a_width"> <xsl:for-each select="data"> <xsl:sort select="string-length(@a)" data-type="number" /> <xsl:if test="position() = last()"> <xsl:value-of select="string-length(@a)" /> </xsl:if> </xsl:for-each> </xsl:variable> This is the general method of picking from an ordered list of derived values in XSLT 1.0. If you want to pick the minimum/maximum from actual (natively sortable) values, you can take...

Fixed element in android?

android,xml,android-fragments

You need a FrameLayout. In a FrameLayout, the children are overlapped on top of each other with the last child being at the topmost. activity_main.xml <FrameLayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:tools="http://schemas.android.com/tools" xmlns:fab="http://schemas.android.com/apk/res-auto" android:layout_width="match_parent" android:layout_height="match_parent" android:fitsSystemWindows="true"> <LinearLayout android:layout_width="match_parent" android:layout_height="match_parent"...

Foreign key in C#

c#,sql,sql-server,database

You want create relationship in two table Refer this link http://www.c-sharpcorner.com/Blogs/5608/create-a-relationship-between-two-dataset-tables.aspx...

Collect strings after a foreach loop

c#,xml,foreach

Yep, you need to do the adding within the loop. I'd use a List<string> as it supports LINQ: XmlNodeList skillNameNodeList=SkillXML.GetElementsByTagName("name"); List<string> skills = new List<string>(); foreach (XmlNode skillNameNode in skillNameNodeList) { skills.Add(skillNameNode.Attributes["value"].Value); } ...

How to select next row after select in SQL Server?

sql,sql-server

The query can be written as: ; WITH Base AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY Shift_Date) RN FROM #Table1 ) , WithC AS ( SELECT * FROM Base WHERE Shift2 = 'C' ) SELECT * FROM WithC UNION SELECT WithCNext.* FROM WithC C LEFT JOIN Base WithCNext ON...

SQL Server / C# : Filter for System.Date - results only entries at 00:00:00

c#,asp.net,sql-server,date,gridview-sorting

What happens if you change all of the filters to use 'LIKE': if (DropDownList1.SelectedValue.ToString().Equals("Start")) { FilterExpression = string.Format("Start LIKE '{0}%'", TextBox1.Text); } Then, you're not matching against an exact date (at midnight), but matching any date-times which start with that date. Update Or perhaps you could try this... if (DropDownList1.SelectedValue.ToString().Equals("Start"))...

How to use OFFSET and Fetch without Order by in SQL Server

sql-server,sql-server-2012,sql-order-by,fetch,offset

By adding an identity column to the temp table variable declare @TempTable table([some columns], rownr int identity(1,1) ) INSERT INTO @TempTable [some columns] select [some columns] from table1 order by col1 INSERT INTO @TempTable [same columns] select [some columns] from table2 order by col2 An automatic incrementing number is added...

Can someone explain to me how this statement is an exclude?

sql,sql-server,tsql

I can explain... a query that's very close to yours. Let me alter it to: SELECT * FROM [table].[dbo].[one] AS t1 LEFT JOIN [table].[dbo].[one] AS t2 ON (t1.ColumnX = t2.ColumnX AND t2.columnY = 1) WHERE t2.tableID IS NULL This query retrieves all rows from t1, then checks to see if...

Cannot Browse in sql to backup bak file

sql-server

Try this query RESTORE DATABASE Databasename FROM DISK = 'Z:\SQLServerBackups\tms.bak' ; ...

type conversion performance optimizable?

c#,xml,csv,optimization,type-conversion

IEnumerable<string> values = new List<string>(); values = … Probably not going to be a big deal, but why create a new List<string>() just to throw it away. Replace this with either: IEnumerable<string> values; values = … If you need values defined in a previous scope, or else just: Enumerable<string> values...

XSL transformation outputting multiple times and other confusion

xml,xslt,xpath

1) Your template is applied to 3 elements, and for each of them, loops over all the parents li elements (yes, for each of them, ask all the li elements, children of the grand-father of the current content, which are all the 3 li elements, each time). 2) Because that's...

INSERT INTO fails due to incorrect conversion T-SQL

sql-server,tsql

The problem is that there's no implicit conversion from varchar (your literal) to sql_variant. Just add an explicit conversion and you're done: cast('FooBar' as sql_variant) ...

XSLT How to remove style from div and td tags

xml,xslt

To remove some nodes start with the identity transformation template <xsl:template match="@* | node()"> <xsl:copy> <xsl:apply-templates select="@* | node()"/> </xsl:copy> </xsl:template> then add an empty template for the nodes to be removed: <xsl:template xmlns:xhtml="http://www.w3.org/1999/xhtml" match="xhtml:div/@style | xhtml:li/@style | xhtml:td/@style | xhtml:span/@style"/> ...

xpath query seem to be failing

xml,xpath

$str = '<root><Pages> <copyright>me inc. 2015,</copyright> <author>Me</author> <lastUpdate>2/1/1999</lastUpdate> <Home>--------------------</Home> <About>--------------------</About> <Contact>------------------</Contact> </Pages></root>'; $xml = simplexml_load_string($str); $result = $xml->xpath('//Pages/*[(name() = "Home") or following-sibling::Home]'); if ($result === false) { $this->parseError(); //To return xml Error code...

left join table, find both null and match value

sql,sql-server,join

Try FULL OUTER JOIN. This is the sqlfiddle. It will produce the op you are expecting SQLFiddle select t1.years, t1.numOfppl, t2.years, t2.numOfppl from t1 full outer join t2 on t1.years=t2.years ...

Purging Database - Count purged/not-purged tables

mysql,sql,sql-server,database,stored-procedures

The only way to do this is to manually run a count(*) on all of your tables filtering on the particular date field. The reason for this is because one table might have a column "CreatedDate" that you need to check if it's >30 days old, while another might have...

Looping distinct values from one table through another without a join

sql,sql-server,tsql,while-loop

So you want all distinct records from table1 paired with all records in table2? That is a cross join: select * from (select distinct * from table1) t1 cross join table2; Or do you want them related by date? Then inner-join: select * from (select distinct * from table1) t1...

XSLT for-each statement not iterating proper amount of times

xml,xslt

Try it this way? <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:template match="/template/L"> <html> <body> <ul> <xsl:for-each select="Q"> <li> <xsl:value-of select="text()"/> <ul> <xsl:for-each select="R"> <li> <xsl:value-of select="."/> </li> </xsl:for-each> </ul> </li> </xsl:for-each> </ul> </body> </html> </xsl:template> </xsl:stylesheet> Explanation: When...

SQL Customized search with special characters

sql,sql-server,sql-server-2008

Here is my attempt using Jeff Moden's DelimitedSplit8k to split the comma-separated values. First, here is the splitter function (check the article for updates of the script): CREATE FUNCTION [dbo].[DelimitedSplit8K]( @pString VARCHAR(8000), @pDelimiter CHAR(1) ) RETURNS TABLE WITH SCHEMABINDING AS RETURN WITH E1(N) AS ( SELECT 1 UNION ALL SELECT...

Sql injected code is inserted to my database . How to remove it

sql-server

You can use the fact that html code starts with symbol <. Then: UPDATE TableName SET SomeColumn = CASE WHEN CHARINDEX('<', SomeColumn) > 0 THEN SUBSTRING(SomeColumn, 1, CHARINDEX('<', SomeColumn) - 1) ELSE SomeColumn END If this is not true then we will need more information about data. May be it...

Tagging values in HTML document for automated extraction

html,xml,html5

If you are using them as meta-documents and they are sent to the parser, then converted as HTML and as long as the converted HTMLs do not have any irrelevant tags, it is fine! So, if the following code: <requirement> THE REQUIREMENT HERE </requirement> Gets converted into something like: <!--...

Convert contents of an XmlNodeList to a new XmlDocument without looping

c#,xml,xpath,xmldocument,xmlnodelist

If you're happy to convert it into LINQ to XML, it's really simple: XDocument original = ...; // However you load the original document // Separated out for clarity - could be inlined, of course string xpath = "//Person[not(PersonID = following::Person/PersonID)]" XDocument people = new XDocument( new XElement("Persons", original.XPathSelectElements(xpath) )...

Get XML node value when previous node value conditions are true (without looping)

xml,vb.net,linq-to-xml

UPDATE Using an XDocument vs an XmlDocument, I believe this does what you're asking without using loops. This is dependent on the elements being in the order of <PhoneType> <PhonePrimaryYN> <PhoneNumber> string xml = "<?xml version=\"1.0\"?>" + "<Root>" + " <PhoneType dataType=\"string\">" + " <Value>CELL</Value>" + " </PhoneType>" + "...

Connecting to database using Windows Athentication

sql-server,vb.net,authentication,connection-string

You need to add Integrated Security=SSPI and remove username and password from the connection string. Dim ConnectionString As String = "Data Source=Server;Initial Catalog=m2mdata02;Integrated Security=SSPI;" ...