Tag: xml

How to parse XML attribute using MS SQL

I need to get the values of attributes present in XML document using MS SQL query Ex : I have a XML which looks below <trade xmlns=”www.somewebsite.com” Action = “Insert” TradeNumber = “1053” Volume = “25” DateTime = “2013-12-06T10:22:47.497” PNC = “false”> <Specifier Specifierid = “112” Span = “Single” Name = “Indian”/> </trade> I need […]

How to handle & symbol in XML input in SQL Server stored procedure?

I have an update stored procedure which takes @XML as input parameter. The XML is as below: <Root> <data> <FriendlyName>abc</FriendlyName> <URL>http://abc.aspx?view=Approval&Business=XYZ</URL> </data> </Root> When the stored procedure is executed, it throws error XML parsing: line 5, character 141, semicolon expected It is failing because of the & symbol, instead it expects &amp;. So, how to […]

TSQL Join and Group Xml Column Values

I have a table like this; http://i.stack.imgur.com/QkyB6.png I want to sum each first 5 columns and string join with comma values in xml column. (It store results like this : <r><p>example</p><p>example2</p></r>) select sum(Value1), sum(Value2), sum(Value3), sum(Value4), sum(Value5), (select t.c.value(‘.’, ‘varchar(400)’) + ‘,’ from Xml.nodes(‘/r/p’) as t(c) for xml path(”)), DateTime as DateTime from table group […]

Parsing xml file from url using sql server

I am parsings XML file from url, when I parse first time it take correct response , but when in url xml file changed , sql server parse old response (for example in first time something=5 sql server parse 5, then when in url something = 6 sql server parse 5 again). But I think […]

SQL Server 2012 XML Type Preserve Significant Whitespace

I am trying to store some data in an XML-Typed Column which, when the data is a string, will preserve the exact text and not replace or adjust embedded line-endings specifically CR & LF. So a C# string like “A\r\nB\rC\nD” needs to come back exactly so but it would appear that the XML conversion insists […]

How to select xmlnode and value in sql server 2008

I have below xml in table <GetOrdersResponse xmlns=”urn:ebay:apis:eBLBaseComponents”> <Timestamp>2013-09-07T06:38:46.192Z</Timestamp> <Ack>Success</Ack> <Version>837</Version> <Build>E837_CORE_APIXO_16317239_R1</Build> <HasMoreOrders>true</HasMoreOrders> <OrdersPerPage>100</OrdersPerPage> <PageNumber>1</PageNumber> <ReturnedOrderCountActual>48</ReturnedOrderCountActual> <PaginationResult> <TotalNumberOfPages>9</TotalNumberOfPages> <TotalNumberOfEntries>881</TotalNumberOfEntries> </PaginationResult> <OrderArray> <Order> <OrderID>1</OrderID> </Order> <Order> <OrderID>2</OrderID> </Order> </OrderArray> </GetOrdersResponse> I have written below query and getting 2 rows with balank(not null) results select xmlresponse.value(‘(/GetOrdersResponse/OrderArray)[1]’,’varchar(max)’) from ordersxml I want to result like below Row1: <Order> <OrderID>1</OrderID> […]

Retrieve data from a For XML subquery

I am creating a table as follows: CREATE TABLE dbo.Test ( A int, B int ) GO INSERT INTO Test VALUES (1, 11) GO INSERT INTO Test VALUES (5, 55) GO INSERT INTO Test VALUES (4, 44) GO I have a query which converts this into XML as : SELECT A,B FROM Test ORDER BY […]

Parsing non-standard XML in SQL Server with XTbl

I have a serialised piece of data in a column that I want to retrieve a single value from, in the following form: <FirstNode>Something</><SecondNode>Something Else</> I want to retrieve, for example, ‘Something’ from this in SQL Server. I’ve tried the following: declare @data xml; set @data = cast([my data source] as xml); select @data.value(‘(/UserName)[1]’,’varchar(50)’) I’m […]

Sql server : xml truncated while querying in php

While querying a xml data from SQL server database, it returns a truncated xml. $db = DB::get(); $db->exec(‘SET ANSI_NULLS ON’); $db->exec(‘SET ANSI_PADDING ON’); $db->exec(‘SET ANSI_WARNINGS ON’); $db->exec(‘SET ARITHABORT ON’); $db->exec(‘SET CONCAT_NULL_YIELDS_NULL ON’); $db->exec(‘SET NUMERIC_ROUNDABORT OFF’); $db->exec(‘SET QUOTED_IDENTIFIER ON’); $query = “select CAST(db.function(199) AS nvarchar(max))”; $result = $db->query($query); $xml = $result->fetchColumn(); If the length of $xml […]

How to create a flexible query for XML stored in SQL table

I have the following XML file that is copied in my database <ns2:procedure1 xmlns:ns2=”http://www.endpoint.com/”> <auth> <company>TEST Company</company> <lineOfBusiness>Sales</lineOfBusiness> <caseNumber>00001</caseNumber> <creationDate>2013-12-04</creationDate> <reviews> <reviews> <reviewNumber>ZA1010</reviewNumber> <revision>1</revision> <sequence>1</sequence> </reviews> <reviews> <reviewNumber>ZA1010</reviewNumber> <revision>2</revision> <sequence>2</sequence> </reviews> <reviews> <reviewNumber>ZA1010</reviewNumber> <revision>3</revision> <sequence>3</sequence> </reviews> </reviews> </auth> </ns2:procedure1 xmlns:ns2=”http://www.endpoint.com/”> I am using the following code: DECLARE @XML AS XML DECLARE @hDoc AS INT DECLARE […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.