SQL Server 2012 :: Read XML Data With Related Attributes / Properties
Dec 4, 2013
I have a table with one of the column of xml type. the column contains xml like given below. I want to read this xml from the table and show as below with T-sql query
"EmployeeID" "IndustryDome" "description "
Where Description value comes from the value of AllDome/ITEM/Dome /Description whose Dome equals to IndustryDome value
EX:
1166586 3951LX01 Description10
<GetEmployeeDetails xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<return xmlns="http://applications.apch1.com/webservice/schema/">
<EmployeeID>1166586</EmployeeID>
<BankAccounts>
[Code] ....
View 2 Replies
ADVERTISEMENT
Nov 26, 2007
OBJECTIVE: I would like to read a text file from SQL Server 2000, read the text file content, and load its conntents in a RichTextBoxTHINGS I'VE DONE AND HAVE WORKING:1) I've successfully load a text file (ex: textFile.txt) in sql server database table column (with datatype Image) 2) I've also able to load the file using a Handler as below: using System;using System.Web;using System.Data.SqlClient;public class HandlerImage : IHttpHandler {string connectionString;public void ProcessRequest (HttpContext context) {connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["NWS_ScheduleSQL2000"].ConnectionString;int ImageID = Convert.ToInt32(context.Request.QueryString["id"]);SqlConnection myConnection = new SqlConnection(connectionString);string Command = "SELECT [Image], Image_Type FROM Images WHERE Image_Id=@Image_Id";SqlCommand cmd = new SqlCommand(Command, myConnection);cmd.Parameters.Add("@Image_Id", System.Data.SqlDbType.Int).Value = ImageID;SqlDataReader dr;myConnection.Open(); cmd.Prepare(); dr = cmd.ExecuteReader();if (dr.Read()){ //WRITE IMAGE TO THE BROWSERcontext.Response.ContentType = dr["Image_Type"].ToString();context.Response.BinaryWrite((byte[])dr["Image"]);}myConnection.Close();}public bool IsReusable {get {return false;}}}'>'>
<a href='<%# "HandlerDocument.ashx?id=" + Eval("Doc_ID") %>'>File
</a>- Click on this link, I'll be able to download or view the file WHAT I WANT TO DO, BUT HAVE PROBLEM:- I would like to be able to read CONTENT of this file and load it in a string as belowStreamReader SR = new StreamReader()SR = File.Open("File.txt");String contentText = SR.Readline();txtBox.text = contentText;BUT THIS ONLY WORK FOR files in the server.I would like to be able to read FILE CONTENTS from SQL Server.PLEASE HELP. I really appreciate it.
View 1 Replies
View Related
Mar 16, 2015
I have an ODBC connection string that is working fine with the following properties:
Database="XXXXXXX",Network="YYYYYY"; strangely no server is specified in the string, but it is specified in the ODBC Connection file.
I am trying to do a new server registration in SSMS for this database.However, I don't understand where the network spec is placed.
Under Registered server name I've tried:
YYYYYYXXXXX
When I browse the server for the database instance list, I receive "network path was not found".
I even tried:"XXXXXXX",Network="YYYYYY" for the registered server name.Same error message.
What am I doing wrong ?
View 1 Replies
View Related
Jun 30, 2015
I am working in SQL Server Master Data Services  Version 11.0.5058.0 (SP 2).
I have been asked to group all the financial attributes together. Â When I move one of the attributes up using the arrows, it works good jumping over one attribute at a time. Â Then I reach a section of attributes where it leap frogs over 24 attributes.
It appears these 24 attributes are in a subgroup but there are no attribute groups and I removed the subscription view from the entity. Â If I move one of the 24 attributes in the group, it moves it outside of the 24 attributes.
This is under leaf member attributes. Â There are no collection or consolidated groups.
View 2 Replies
View Related
Jun 15, 2004
Apart the IDENTITY property, what other properties or attributes are not transferred to the target schema?
I know that one can use NOT_FOR_REPLICATION for identities, but I am interested in a (complete?!) list of metadata objects that transactional replication *prefers* not to transfer across to the target by default.
TIA
View 2 Replies
View Related
May 23, 2014
How do I SELECT multiple nodes with same name but different attributes. For example, I would need to SELECT NM1.03.1 from <NM1> node. How do I specify which one I want to SELECT? What if I want to SELECT them all? What if I only want two of them?
<NM1.03.1>SAMPLE INC</NM1.03.1>
<NM1.03.1>PPO BLUE</NM1.03.1>
<NM1.03.1>EDI SPECIALTY SAMPLE</NM1.03.1>
<NM1.03.1>CUSTOMER</NM1.03.1>
--Sample Code
<X12Transaction elementDelimiter="*" segmentDelimiter="
" subelementDelimiter=">">
<ISA>
<ISA.01>
<ISA.01.1>00</ISA.01.1>
[Code] ....
View 9 Replies
View Related
Jul 15, 2014
I am not very familiar with working with spatial data and I am currently trying to work out how to obtain some properties of a circle defined as a geometry data type. Specifically, I need to determine x and y co-ordinates for the centre point and the diameter of the circle.I have had a look at the MSDN reference for spatial data .
View 4 Replies
View Related
Sep 29, 2015
I have a table that has multiple transactions for stock items.
This table holds all records relating to items that are inducted onto the system and there movement. For each stock item i am interested in getting the drop destination, if it has one, and only when it follows the sequential order of "Inducted>OnTransport>Dropped" (this sequence isn't always the case). Also note the CreatedDate for the Inducted and OnTransport records for the valid sequences are always the same. Below is a valid sequence for a stock item so i would want to return 'Lane01' for the Destination of this occurrence of the stock item, if this item didn't have a valid drop location then destination would be blank. Also note each stock item can be inducted more than one time per-day.
I think i have managed to build the below sql but it will only do one item at a time, so would have to wrap it in a function. Is there a way of writing a set based select statement that gets all the inducted items and for the ones that do follow the "Inducted>OnTransport>Dropped" return the destination it was dropped at? I've attached scrips below:
DECLARE @StockItemID nVarchar(128)
DECLARE @CreateDate DATETIME
Set @StockItemID='8cbe17da-6079-4170-b27a-41c0d38830f6'
Set @CreateDate = CAST('2015-08-31 13:52:39.890' AS datetime)
[Code] ....
View 9 Replies
View Related
Apr 25, 2007
Hi everyone,
I€™d like to read the SqlStatement property for a Sql Task from a Script Task (previously Sql Task is executed) in execution.
Keeping on mind that such SqlStatement receives an input parameter.
Is it possible? I think so but how do I such thing?
Let me know if you need further details.
Thanks a lot for your time and thoughts,
View 1 Replies
View Related
Aug 10, 2015
I've created an excel spreadsheet with a data connection. This data connection uses a query that runs against a read-only database.
The issue I'm having is that the query never seems to finish running against the database, whether I open the Excel spreadsheet to view the data or run the query in SSMS.
I created the connection on the Data ribbon by going to From Other Sources --> From SQL Server and using the Data Connection Wizard.
Is there some kind of setting or property I'm missing that would allow this query to finish running?
View 6 Replies
View Related
Jun 12, 2015
I have a test server (TEST1) running SQL 2012 and Windows 2012R2. One of the developers wants to use OPENROWSET to read in data from an Excel 2010 file (an xlsx file).
I have loaded the Microsoft Drivers in "AccessDatabaseEngine_64.exe" and enabled the Ad Hoc Distributed Queries option in SQL.
This is the sample code we are working with:
SELECT
X.MEMBID
FROM OPENROWSET(
'MSDASQL',
'Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=etworkserverFolder1Folder2MEMBIDs.xlsx',
'SELECT * FROM [Sheet1$]'
) AS X
I can run the sample query from my laptop with SSMS (I have admin rights) and I can also run it as SA from my laptop. So all is good, right?
But if I RDC into TEST1, I cannot run the query. I get this error:
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Excel Driver] Your network access was interrupted. To continue, close the database, and then open it again.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Excel Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x574 Thread 0xb74 DBC 0x1d07f08 Excel'.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Excel Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x574 Thread 0xb74 DBC 0x1d07f08 Excel'.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".But wait! It gets better.
I can run the query as SA from TEST1.
And of course, the developer can't run it either.
And it works fine in the production server.
I'm thinking the basics are there but something isn't right in some permission somewhere.
View 9 Replies
View Related
Mar 24, 2015
How to identify whether the files are in read write or read only?
View 1 Replies
View Related
May 24, 2015
I'm using a DW from Northwind database to build a cube to do some analitical taks. I already create the cube and now I am "cleaning" the dimensions. I'm having some difficults to understand the logical off this part. The reason is that When I create the Data Source View, I only import the Foreign Keys that connect the Dimensions to Fact_Table. I have to drag the attributes of Dimension from Data Source View to the tab attributes?Â
Imagine this:
I have the following dimension:
Dim_Customer:
Customer_ID
Name_Customer
Job_Function
Date_of_Birth
Contact
Address
City
Country
When I create the cube only Customer_ID appears in attributes tab, it's normal?Â
One more question:
I don't want to create a hierarchy like:
Customer ID -> Name_Customer
Customer ID -> Date_of_Birth
Customer ID -> Address
Customer ID -> City
Customer ID -> Country
My idea is to create the following hierarchy:Â
Name_Customer ->Â Date_of_Birth ->Â Â Address ->Â Â City ->Â Country
But the first hierarchy that I show is always appears to me. Do you know what is happens?
View 2 Replies
View Related
Jun 27, 2014
i have a database which get refreshed every day from client's data . and we need to pull heavy data from them every day as reports . so only selects happens on that database.
we do daily population of some table in some other databases from this daily refreshed DB.
will read uncommitted or NOLOCK with select queries to retrieve data faster.
there will be no dirty read as there are NO DML operation in that database so for SELECT which happens concurrently on these tables , will NOLOCK work?
View 2 Replies
View Related
Aug 15, 2014
Can a user of db owner role of a database change the databse option to read only and read-write?If not what permission I need to grant to the user?
View 1 Replies
View Related
Oct 29, 2014
I'd like to create a table that will store different order items. Several order items make up one single order. Order items can have 0 or more children (max depth will never be deeper than one). Order items can have up to 150 attributes/values. The way I think this should be done is using XML column instead of the EAV type of model. My table structure currently looks like this:
* child_order_item_id (PK)
* parent_order_item_id (FK to child_order_item_id)
* order_id (FK to Order table)
* product_id (FK to Product table)
* price
* attribute_XML
How my attribute_XML should look like or how to validate the xml.
View 2 Replies
View Related
Nov 21, 2013
I have the following code and trouble reading values of Bank Accounts. If i remove the line it says "xmlns="http://applications.apch1.com/webservice/schema/" then i my query is working. But i cant remove this becasue that is what i will get response from a web service. All the records are stored in the database with this line included.
DECLARE @MyXML XML
SET @MyXML = '<GetEmployeeDetails xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<return xmlns="http://applications.apch1.com/webservice/schema/">
<CustomerID> A8339 </CustomerID>
<BankAccounts>
[Code] .....
View 2 Replies
View Related
Jul 18, 2014
Considering trying to move 2008 acitve/passive cluster with log ship to day old read only 2008 server to 2012 active/passive to 2012 AG Read only server. Only problem is that read only instance may have to be a 2nd instance on a server. The new box is a beast 64 core 256 gig of RAM hp so this is no dog. So I have these choices
migrate 2008 active/passive cluster to 2012 active passive (this will be it's own ordeal)take new monster box and build two instances, one that will run the AG read only database, the other will house reporitng services and analysis services and a few dw databases. We are not heavy into deep dive analysis services yet kind of in it's infancy. Not sure if this other instance will be sql 2008R2 , may be able to do 2012. MY also have a few small sharepoint databases but they barely use it.
View 3 Replies
View Related
Jul 14, 2004
Hi I have a question how to load data to tables linked by Foreign Keys in MSDE/SQL server. Example:
If I have 2 tables linked (by Foreign Key):
One table:
ITEM idITEM NAMEITEM CATEGORY (FK)
1cheese 2
And another:
Category IDCATEGORY NAME
1 household
2 food
3 general
How do I enter the load of data
Do I have to enter it as
1cheese2
or is there some way of entering it as
1cheesefood
TDS wizard does not allow me to transfer to views/querries what I thought would be a normal way as I would enter data to view(relevant to Access's form) and it would update related tables . When I wrote sql to do it it said I can not update my view table as too many tables would be affected(I had lookup tables empty then though)
I am doing it by number using TDS wizard to transfer it directly to the main table but there must be a better way
View 1 Replies
View Related
Aug 3, 2015
I have built a fact table and few dimension views in Datamart with the aim of creating a Cube.
On the Fact table I have added a CASE Statement with the following threshold for Premium due amounts:
CASE WHEN....
'Due_0-1_Month'
'Due_1-2_Month'
'Due_2-3_Month'
'Due_Over_3_Months'
'Overdue_0-1_Month'
'Overdue_1-3_Month'
'Overdue_3-6_Month'
'Overdue_Over_6_Months'
...END
I then created a Dimension to link this to:
CREATE VIEW...
Select 'Due_0-1_Month' as Ageing_Threshold
union all
Select 'Due_1-2_Month'
union all
Select 'Due_2-3_Month'
[Code] ....
I was successful in processing the cube, however the problem is everytime I drag the dimension on the columns field in Pivot tables the Thresholds start to break up the other amounts that I have on display like Acquisition Costs, Tax amounts. I am only interested in showing the breakdown of Premium amount measure by the Threshold dimension.
somehow 'Hide' or 'prevent' the Threshold dimension from breaking down the other measures on the Pivot and only breakdown the amounts for Premium?
how I should structure my tables in SQL or any MDX queries to resolve this.
View 0 Replies
View Related
Jan 9, 2005
Does anyone know how to display the properties of a column / data field in ASP? Thanks!
View 6 Replies
View Related
Nov 11, 2004
Hello all-
I have a specification table that has some attributes defined.
SpecId - Id of the specification
Attribute - Attribute of the spec. (Like Color, HP etc)
Value - Is the value of the attribute
Then I have a car table that actually has information about the cars. Intention is to take each specification and match the cars that match the specification. If the car has more attributes than the spec, we ignore the extra attributes for the match. But if the car has less attributes, we don't even consider the car as a match (even if the attributes present, match). To summarize, the car's attributes should be >= spec's attributes.
The code I have below is bad because I am joining the same tables twice. In addition, it fails in the condition "the car's attributes should be >= spec's attributes"
Any help is greatly appreciated.
DECLARE @Specification TABLE
(SpecId VARCHAR(10),
AttributeVARCHAR(100),
ValueVARCHAR(100))
DECLARE @Car TABLE
(CarName VARCHAR(10),
AttributeVARCHAR(100),
ValueVARCHAR(100))
INSERT INTO @Specification VALUES ('S1', 'Type', 'Sedan')
INSERT INTO @Specification VALUES ('S1', 'Transmission', 'Auto')
INSERT INTO @Specification VALUES ('S1', 'HP', '220')
INSERT INTO @Specification VALUES ('S2', 'Type', 'SUV')
INSERT INTO @Specification VALUES ('S2', 'Transmission', 'Manual')
INSERT INTO @Specification VALUES ('S2', 'HP', '300')
INSERT INTO @Car VALUES ('Accord', 'Type', 'Sedan')
INSERT INTO @Car VALUES ('Accord', 'Transmission', 'Auto')
INSERT INTO @Car VALUES ('Accord', 'HP', '220')
INSERT INTO @Car VALUES ('Accord', 'Color', 'Black')
INSERT INTO @Car VALUES ('Escape', 'Type', 'SUV')
INSERT INTO @Car VALUES ('Escape', 'Transmission', 'Manual')
INSERT INTO @Car VALUES ('Escape', 'HP', '300')
INSERT INTO @Car VALUES ('Explorer', 'Type', 'SUV')
INSERT INTO @Car VALUES ('Explorer', 'Transmission', 'Manual')
SELECT DISTINCT Spec.SpecId, Car.CarName
FROM @Specification Spec
INNER JOIN @Car Car
ON Spec.Attribute = Car.Attribute
AND Spec.Value = Car.Value
WHERE Spec.SpecId NOT IN (SELECT Spec.SpecId
FROM @Specification Spec
LEFT OUTER JOIN @Car Car
ON Spec.Attribute = Car.Attribute
AND Spec.Value = Car.Value
WHERE Car.CarName IS NULL)
View 2 Replies
View Related
Nov 11, 2014
I have an excel file which has dynamic columns
i.e. Col1, Col2, Col3 this week. next week i will have a new Col4 in the sheet. This will keep on adding every week.
My problem is to Unpivot the data
Date 8/2/2013 8/9/2013 8/16/2013
Stock 1,561 1,661 1,761
i.e. the abobe table should become as
Date Stock
8/2/2013 1561
8/9/2013 1661
8/16/2013 1,761
How can I unpivot the dynamic columns given that the columns will keep on increasing every week.
View 1 Replies
View Related
Oct 6, 2015
How do I determine the read/write frequency on a database table? I am trying to do this on a 2012 and 2008 R2 servers.
View 2 Replies
View Related
Apr 18, 2007
I have connected to the SQL Server 2005 instance usign the SQL Server Management Studio; I have changed the default locations for the database and log files. I have also rebooted the machine. When I look in the SQL Server Configuration Manager in the properties for the SQL Server (MSSQLSERVER) I see that the data path is still set to the old value and this field is read-only. How can this can changed without going through the registry?
View 10 Replies
View Related
Apr 16, 2008
Hi all,
I have a column in SQL server which is of type ntext. Selecting the specific column to view it in report builder, an error message appears with the following description:
- Cannot run this report. The grouping expression 'nameofcolumn' returns the datatype binary. The Grouping Expression cannot return binary data.
Report Builder recognises this as if it was an image...
Thanks in advance!
View 2 Replies
View Related
Jul 14, 2014
Need to know some of the DMV'S and System Tables related to Database Mirroring.
View 2 Replies
View Related
Oct 11, 2006
Hi,
I am using CRM 3.0 and have a requirement to list all the the tables, attributes names and display names and datatypes. Is there any easy way to export this information from the CRM tool or prepare a SQL query that will list the information?
View 1 Replies
View Related
Sep 3, 2015
I just joined a bank related IT department where the existing IT team is already working on creating a new application since a year now and designed database on SQL Server 2012, the amount (currency related) column's datatype found different in tables some where it is decimal and somewhere found different. I want to suggest them Money datatype to choose where we are dealing with currency related columns. My question is, is that correct to choose Money datatype or should I ignore this?
View 3 Replies
View Related
Nov 16, 2011
I'm trying to write a stored procedure that will parse XML attributes and populate columns within a DB with the stripped data. I'm a complete novice who prior to this week knew nothing about SQL commands, My understanding at least is that I need to perform a bulk insert.
Example XML file:
<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE Asset_Collection SYSTEM "Asset_Collection.dtd">
<Asset_Collection>
<Collection_Metadata
Name="Asset Collection"
Description="Random XML Feed Test"
[Code] ....
Table/Columns which need to be inserting into:
Table:
TABLE_A
Columns:
ProdID
CustomerID
AreaCode
View 4 Replies
View Related
Nov 16, 2006
This is a general question about data modeling. I'm more curious than anything else.
There is much talk about over-training data model, and I'm sure there are under training as well. As a rule of thumb, depending on the algorithm, what is a good ratio of attributes vs data points?
-Young K
View 4 Replies
View Related
Dec 6, 2014
I store files in db in sql server 2008 by filestream. But when a column would be added to table which have filestream, properties of table would be changed. by every things change on table, retrieve files will faced to error. but store process work probably.
and filestream filegroup at following address will be empty. why?
Right click on table --> properties --> storage --> filestream filegroup
View 0 Replies
View Related
Sep 19, 2007
Here is my task I am storing pdf's in sql server. I would like to retrieve the binary data from sql server and write the pdf content into an existing aspx page to the appropriate pageview section. What is the best way to handle this. The code works below but it loads a new browser with the content. I need it to appear in it's tabbed section in the original aspx file. Any assistance you can give me would be greatly appreciated.
Thanks Jerry
oSQLConn.Open()Dim myreader As SqlDataReader
myreader = myCommand.ExecuteReader
Response.Expires = 0
Response.Buffer = True
Response.Clear()
Do While (myreader.Read())
Response.ContentType = ("application/pdf")Response.BinaryWrite(myreader.Item("img_content"))
Loop
View 5 Replies
View Related
