Tag: xml

Using XML as a method parameter in Codefluent

In an SQL 2014 database I have a stored procedure which uses XML as a parameter. I’m attempting to create a Codefluent Method that will use XML as a parameter type. The parameter is defined: <cf:parameter typeName=”xml” name=”XMLcriteria ” /> This results in the DB procedure having the parameter defined as: @XMLcriteria [nvarchar] (256) instead […]

Select from xml file stored procedure running infinitely returning thousands of duplicate records

I have this stored procedure which returns a lot of duplicate records due to wrong cross apply i suppose. Take a look at the screenshot of result set. Case number 000110-54-3 is only hexane but I get other chemical names related to other case numbers also listed for the same case number. Thus, the query […]

Updating XML value within MSSQL

replacing XML tag value within a large XML text value MSSQL. Within MSSQL I have a column called form which is a text column with an extremely large XML. I need to find a certain tag and change the value of that sub tag within the tag from False to True. This is what I […]

Get SQL records based on XML attribute values

I have done some basic XML querying using T-SQL but I am not sure how to attack this problem. I would like to query all rows in a table where a specific XML attribute has a requested value. Here is my table schema: CREATE TABLE [dbo].[Applications_Submissions] ( [Id] [int] IDENTITY(1,1) NOT NULL, [SubmissionGuid] [uniqueidentifier] NOT […]

Working with OpenXML

I am passing XML to SQL Server stored procedure and trying it to store in #temp table. The stored procedure is given below: EXEC sp_xml_preparedocument @i OUTPUT ,@XMLDOC SELECT Id ,UserId ,ModifiedOn ,ModifiedBy ,ModifiedIp ,DetailId INTO #temp FROM OPENXML(@i, ‘/Root/Rec/Detail’, 1) WITH ( id INT ‘../@Id’ ,UserId INT ‘../@UserId’ ,ModifiedOn DATETIME ‘../@ModifiedOn’ ,ModifiedBy INT ‘../@ModifiedBy’ […]

XML parsing Error Illegal qualified name character

I’m using SQL Server 2008 Management Studio and I try to insert some XML data into my table. I’ve never done that before can you please explain me why SQL is throwing an error? Here’s my data: select IDPatient, IDObjet, Nplan, dateCreation, dateModification, actif, case when ISNULL(unite_20,”) + ISNULL(date_20,”) <> ” then 20 when ISNULL(unite_19,”) […]

Need to select only those attribute values starting with “name” and conditional check

Please suggest on below XML data: <data> <x name=”name0″>val1</x> <x name=”other0″>Yes</x> <x name=”name1″>val2</x> <x name=”other1″>Yes</x> <x name=”name2″>val3</x> <x name=”other2″>No</x> <x name=”name3″>val4</x> <x name=”other3″>No</x> … … </data> Need to select only those attribute values starting with “name” for 0,1,2,3… whose respective “other” values 0,1,2,3 are “Yes” …end to end mapping on 0,1,2,3… for “name” & “other” […]

How to combine multiple XPath queries into one

i have a table like this create table [Trades] ( [Id] int not null ,[TradeXML] xml not null ) here’s the contents of the TradeXML xml column <Trade> <Arg> <Name>Id</Name> <Data> <DataItem DataType=”8″>012345678</DataItem> </Data> </Arg> <Arg> <Name>Function</Name> <Data> <DataItem DataType=”8″>CapFloor</DataItem> </Data> </Arg> <Arg> <Name>Curve</Name> <Data> <DataItem DataType=”8″>EURCurve</DataItem> </Data> </Arg> <Arg> <Name>Rates</Name> <Data> <DataItem DataType=”5″>2.175</DataItem> <DataItem […]

Repeat first element XML SQL Server using for path

I’m generating an XML where the first element needs to be repeated. Currently my code gives following result: <Item> <CompanyCode>VC</CompanyCode> <ProfitCenterCode>110050</ProfitCenterCode> <ProfitCenterName>Waardeverminderingen Omzet</ProfitCenterName> <ExternalCode>VC_110050</ExternalCode> <Active>1</Active> <Dependencies> <Dependency> <ExternalCode>VC_85</ExternalCode> <BusinessUnitCode>85</BusinessUnitCode> <Dependencies> <Dependency> <ExternalCode>VC_K0570</ExternalCode> <CostCenterCode>K0570</CostCenterCode> </Dependency> </Dependencies> </Dependency> <Dependency> <ExternalCode>VC_86</ExternalCode> <BusinessUnitCode>86</BusinessUnitCode> <Dependencies> <Dependency> <ExternalCode>VC_K0570</ExternalCode> <CostCenterCode>K0570</CostCenterCode> </Dependency> </Dependencies> </Dependency> </Dependencies> </Item> What I want is following code: <Item> […]

Looping through XMLs in folder using openrowset / “Must declare the scalar variable” Issue

Essentially I am trying to use SQL to query XML files within a folder. The code to query the XML works fine on its own but once I turned it into a string to allow for expressions in ‘openrowset‘ it keeps saying throwing out an error message saying it cannot find @x and that ‘Must […]

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