Read XML Node Elements Using Query

Dec 29, 2011

I am having the example xml with the data as shown below,

declare @x xml
set @x = '<SinterklaasWishlists>
<child>
<name>Tim</name>
<wishlist>
<article>
<artno>21491269</artno>

[Code] ....

I want to extract the elements of xml using sql query and insert the data into the table as shown below:

CHILDNAME ARTICLE_NUMBER DESCRIPTION PRICE
------------------ ---------------------------- ----------------------------------- -----------
Tim1 21491269 Crane 12.50
Tim1 21499517 Keyboard 10
Tim1 21521591 Crime Investigation Game 9.95
Tim2 3145678 Mouse 12.50

View 2 Replies


ADVERTISEMENT

How To Read The Xml Returned By Select Query With For XML Auto,Elements

Dec 13, 2007

Hi all,
 I am writing a select query which produces huge xml data.Now i want to read that data from my web application a save it as xml file.
How can i do that.
I am using asp.net vb.net.

View 1 Replies View Related

T-SQL (SS2K8) :: How To Read First XML Node

Jun 4, 2014

its possible to check what the first node is before loading an XML file. The file name is just a combination of random numbers e.g. TMP_18028423.XML there's no way of knowing what it contains I need to check before loading into a table. Then I'll process the data from the temp table. i need to check if the first node is <Departments> then load the file that contains departments.

Here's the script I'm using to load the File:

CREATE PROCEDURE JobDepartmentXMLImport
@FileName varchar(256)
AS
BEGIN
--Create temp table
CREATE TABLE #TempDepartment(
[DepartmentName] nvarchar(64) NULL,

[code]....

View 2 Replies View Related

SQL Server 2012 :: Read All Values From XML Node?

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

New To This! How Do I Retrive Elements In My Query Instead Of The Discription Of The Dataset?

Oct 10, 2007

View 3 Replies View Related

SQL 2012 :: Log Shipping - Reboot Secondary Node And Then Primary Node

Apr 20, 2015

I have not used log shipping before and find myself in a position where I need to reboot the secondary node and then the primary node and I don't actually need to failover.

Is there anything I need to be aware of. When rebooting the secondary node I assume the transactions will be held in the primary nodes log till the secondary comes back and just carry on once back up?

When rebooting the primary node nothing needs to be done and the log shipping will just start again once it has come back?

View 3 Replies View Related

Do I Need To Install SQL Server To Second Node In Cluster Prior To Adding The Node?

Jun 12, 2007

I read these instructions:

http://msdn2.microsoft.com/en-us/library/ms191545(SQL.90).aspx



But I'm not sure if I have to install SQL Server first on node 2, then add it to the cluster. Or does adding it to the cluster also install the software?



Thanks

View 1 Replies View Related

SQL 2012 :: Running 2 Distinct AGs On Each Node Of A Two Node Cluster?

Oct 23, 2014

I'm contemplating running two availability groups on a two node WSFC. The WSFC is setup with a file share witness (i.e. no shared storage). Can I safely run 1 AG on one primary node, and the other AG on the other node (as primary). Each AG would have replicas on the passive node. This would effectively allow both servers to be in use at the same time. In a failover event, I understand that both workloads would transfer to a single server - so the box needs to be sized appropriately.

View 1 Replies View Related

SQL 2012 :: 3 Node Cluster Runs With 2 Node Failures?

Jun 11, 2015

We are in the process of building a 3 node SQL Server Cluster (Server 2012/ SQL Server 2012), and we have configured the quorum so that all 3 nodes have a vote (no file share witness as we already have an odd number of nodes).

As I understand it, this should allow the cluster to run as long as 2 of the nodes remain online.

However, the validation report states that 2 node failures would be acceptable and, when we tested this by powering off two of the nodes, the cluster did indeed continue to run on a single node.

View 4 Replies View Related

Transfering Cluster Resources From One Node To Another Node

Aug 21, 2007



I configure Windows 2003 R2 and SQL 2005 two nodes Cluster. When I move cluster resource from one node to anther node it takes around 30 seconds to become online. So in that time if any query is running it stops responding.



So please suggest in this regard

View 2 Replies View Related

How To Get Node Name In SQL 2005 Nodes Query? Thanks

Nov 30, 2006

for example,xml like
'<name>test</name><age>10</age>'
select ? from @xml.nodes('/*')
how to get the node tag name like name and age?
thanks

View 4 Replies View Related

Is There A Way To Get The Node Descrption In The Same Query Where You Get The Prediction?

Feb 8, 2008

I am doing this right now this way:
1) I do the DMX prediction query where I get the PredictNodeId(predict_var), my query is like this:

SELECT PredictNodeId(predict_var), model_1.predict_var, t.var_1, t.var_2 FROM model_1 PREDICTION JOIN OPENQUERY([DATA_SOURCE_1], 'SELECT var_1, var_2 FROM table_1') AS t ON model_1.var_1 = t.var_1 AND model_1.var_2 = t.var_2
2)I do the DMX query to get the node_description from the model.content iterating each row from the result of my prediction query, this query is like this:

SELECT node_description FROM model_1.content WHERE node_name = 'node_name_var'

In this query node_name_var = PredictNodeId(predict_var) from my prediction query.
What I want to know if there is a way to merge Query 1 and Query 2 so I can get the node_description in the same query qhere I get the PredictNodeId.

Thanks

Juan José Jara

View 3 Replies View Related

How Do I Write A Query To Get The Path From Ancestor To Node In A Tree?

Sep 12, 2005

Hi all. Here's my problem: I have a tree linking macaddresses (bigints)in a tree structure. i want to get the path from node a to b.create table tree1(father bigint , child bigint);insert into tree1 (father,child) values (100,200);insert into tree1 (father,child) values (100,300);insert into tree1 (father,child) values (100,400);insert into tree1 (father,child) values (200,2000);insert into tree1 (father,child) values (200,3000);insert into tree1 (father,child) values (100,4000);insert into tree1 (father,child) values (2000,11111);you can see that 100 --> 200 --> 2000 --> 11111select * from tree1what i would like is a query that given two parameters returns the pathbetweenthem, in the case of 100,11111 i want to get100200200011111if possible as different rows, but columns will do to.of course i do not know the legnth of the path. it can be very bigthx in advanceTzvika

View 3 Replies View Related

How Do You Determine The Active Node Inside A Stored Proc For An Active/passive 2-node Cluster?

Jan 16, 2008

I invoke xp_cmdshell proc from inside a stored procedure on a 2-node active/passive SQL 2005 SP2 Standard cluster. Depending on which server the xp_cmdshell gets executed on I need to pass different arguments in the shell command. I thought I could use host_name() function to get the runtime process server, however, I am finding that it's not behaving correctly. In one example I know my active node is server2, but the host_name() function is returning server1. The only thing that I could possible explain this is that the MSDTC cluster group is not always on the same active node as the SQL server group and in the case I am talking about the cluster groups are in this mode (differnet nodes). Does the xp_cmdshell get executed by the SQL active node or the MDTC active node? And what is the best way to find out which server is going to run my xp_cmdshell?

Thanks.

Edit:

Perhaps another by product of this is that if I run select host_name() from the Studio Management query window i get different results depending on which server I am running the Studio Management on. On server1 I get server1 and on server 2 I get server 2, all the while server2 is the active node. I need a different function that will always let me determine the correct server that'll be running the xp_cmdshell...

Edit 2: I guess I could determine the running host inside the command shell itself, but I am curious to see if i can do it (cleaner) from SQL.

View 1 Replies View Related

Transact SQL :: XML Elements To Row

Jul 14, 2015

extracting data from XML

DECLARE @xml XML = '<event_result>
<custom_elems>
<custom_elem>
<name>test_01</name>
<value>25</value>
</custom_elem>

[code]...

View 3 Replies View Related

Read File Size In Sql Query

May 23, 2008

hi
how can read file size in sql query

View 2 Replies View Related

Query/read Database Size?

Jul 20, 2005

I have a web-based admin section for a site and I would like to be able toquery the SQL Server database size and display it within my admin area. Isthere a function or method of doing this? My database is hosted by a thirdparty...I appreciate any tips or advice you can provide!Rob

View 5 Replies View Related

Password To Read/write My Query

Feb 6, 2008

I use excel as an interface to write query to retrieve data from a database in network drive. My problem is everyone can open and edit my query. Of course, the univeral database access user name and password will ask but everyone know this.
How can I put password to prevent any query modification?

Thanks
Daniel

View 1 Replies View Related

DTS Package Elements At Run Time Through VB6

Jan 31, 2004

How can i configure DTS package elements at run time through VB6..?
Like Specifying the Source,Destination etc

View 1 Replies View Related

Drop Down List With Many Elements

May 31, 2007

Hello,

I am using SSRS 2005 SP2 and I need to allow my users to select the products they want in the report.
I created a data set to get the list of the products and then used it in a multivalue parameter to generate a drop down list.
My problem is that the list is too long (more than 2000 elements) and thus, it takes 10 sec to collapse the drop down list after tickinging the products.

Does anyone have an miraculous solution?
Thanks in advance for your answers.

Zoz

View 8 Replies View Related

Display Only Time Elements

Jan 18, 2008

This one I hope is easy, but I've been working late and it seems to be aluding me. I have a field called time_in which is a char(6) field type. This field holds time elements such at 10:57

My query below is substracting one hour from Time_In and returning it as Hold_Time. I only want to return the time element without the date portion or the milliseconds, etc.



Code Block
select substring(cu.time_in, 1,5) as time_in,
DATEADD(hour, -1, substring(cu.time_in, 1,5)) as hold_time
from reports.careunits_hold_hours cu



[Current Result Set]

Time_In hold_time
10:57 1900-01-01 09:57:00.000

[Desired Result Set]

Time_In hold_time
10:57 09:57

View 3 Replies View Related

Reuse Of Report Elements

Jun 5, 2007

I am working on a project developing a fairly large number of reports with a team of developers. Many of these reports have common elements and code, such as common headers with user-selectable colors. Additionally, many of the common parts of the reports are at mockup stage currently, and many features will have to be added to the reports as time goes on.



We're attempting to create a generalized framework that will minimize the duplication of effort as we develop these reports, and as we go back and modify or fix them later.

What is the best way to approach this?



My first attempt was to create a report template and base all the reports off of the same template. That was fine for the first pass, but as we need to make changes later, they will not be propagated to the already existing reports.



My second attempt was to have each componant of that template reference a subreport, so that changes to the actual report template will be minimized as we go forward. This works great for minimizeing work, but it appears that you lose many features with the use of subreports, and there seems to be a pretty serious performance impact as well. I have posted about one such issue here: Pagination



If anyone has pointers about how to go about this, and where I should start, they would be greatly apreciated!

View 2 Replies View Related

How Can I Read Type Of All Fields In Joined Query?

Nov 29, 2007

How can I read type of all fields in joined query?
Is there a stored procedure method to use for this purpose?
 
Thanks

View 3 Replies View Related

Query Highest Entries From Database - Pls Read More.....

Feb 6, 2006

Hi folks, sorry for the poor explanation.

Im using SQL 2000

I have a database that has a column named 'Initials' in a char in field

I want to be able to return in a query the highest entries if an indiviuals initials & count from the table, so it would display some like this

Initials Count
DRT 51
AMS 49
JJJ 21
PLI 10

Hope u can help, thanks in advance

View 2 Replies View Related

Newbe Question: Need A Way To Read In Large Query With .net

Feb 26, 2007

Could someone please point me in the right direction on how to read ina large query with .net.I am trying to emulate a legacy database system so I don't know theupper bounds of the sql query. An example query would be somethinglike:Select * from invoices where year 1995the query must be updatable and only return say 10 to 100 rows at atime.It should also be forward only and discard rows no longer in use tosave memory.And if at all possible I would like to lock one row at a time as therow is read in.

View 5 Replies View Related

OLE DB Command... Read Returned Fields From Query...

Apr 16, 2007

Dear Friends,

I have an OLE DB Command inside an ETL. I want to execute a stored procedure that returns 2 fields, for example:





CREATE PROCEDURE sp_Name

AS

SELECT Field1, Field2 FROM Table





How can I continue the dataflow? How can I return the values from the query? Returns only a row...

View 10 Replies View Related

FOR XML AUTO Returns Too Many Additional Elements

Jul 23, 2007

Hi, I use the FOR XML AUTO to retrive native XML from a database with:
SELECT [xml] FROM myxml WHERE id = 81 FOR XML AUTO, elements, root('ROOT')"
However it returns the database name and table name as parent elements. How can I return just my raw XML data without additional elements:
XML is Stored:
<ROOT>
 <CHAPTER>
  <TITLE>This is a test</TITLE> </CHAPTER>
</ROOT>
Returns:
<databasename>
<tablename>
<ROOT>
  <CHAPTER>
    <TITLE>This is a test</TITLE>  </CHAPTER>
</ROOT>
</tablename>
</databasename>

View 1 Replies View Related

T-SQL (SS2K8) :: How To Populate More Than One ComplexType XML Elements

Apr 7, 2015

I need to build an XML file with three sibling ComplexType elements, e.g. finance, itinerary and crew details. I can separately build each of them with no problem at all. Below is an example of the finance part, but how to have three within the same XML,?

What I have found so far was only to build one ComplexType, as I did below, but not a combination of them.

WITH finance AS
(SELECT 200.12 AS fare,
12.78 AS VAT,
45.5 AS Tax
UNION ALL
SELECT 150.12 AS fare,

[Code] ......

View 2 Replies View Related

Importing XML With Child Elements Using SSIS

Apr 30, 2014

I am new to SSIS. How to import the below xml in sql server using SSIS?

<?xml version="1.0" encoding="utf-8" ?>
- <Employee>
- <EmployeeData>
<EmployeeID>65938</EmployeeID>
<SID>schauhan</SID>

[Code] ....

View 2 Replies View Related

For XML: Create Elements Using Cell Values

Aug 5, 2005

Hi All,If have a sql table with 2 columns and 2 rows with values[["col1row1","col2row1"],["col1row2","col2row2"]].Using t-SQL with "for xml"How can i create a xml where the cell values (not column names) appearas elements?eg:<col1row1>col2row1</col1row1><col1row2>col2row2</col1row2>Thanks,slyi

View 12 Replies View Related

Stuck With WHERE Clause With Multiple Elements

Jul 20, 2005

HiI'm a bit stuck with a SELECT query. This is a simplified version ofwhat I need. I've had a look in a few books and online but I'mdefinitely missing something. I'm trying to avoid looping and cursors.I'll be running this in a stored procedure on SQL 7.I have a separate query which returns a series of numbers, A, say 101103 107 109 113.I have a table (tableB) with a field myFieldB where I have anotherseries of numbers, B. I want return each row in tableB wherei - ALL values in A existii- ANY values in A existFor ii, I can use WHERE myFieldB IN AHow about for i?Is there a good guide on the web or a book on WHERE clauses and/ormore complex SQL?Thanks in advance!Sam

View 1 Replies View Related

Max Number Of Elements Allowed In An IN Clause.

May 13, 2008

Afternoon,

Can anyone relay to me if there is a max number of elements that are allowed within an IN clause?

Example would be:

SELECT * FROM tblMyTable WHERE ID_Number IN (1,2,3,4,5,...150,000)


where we list all 150,000 numbers between the ( ).

Thanks!

View 4 Replies View Related

Hiding Elements For Print And Export

Aug 25, 2006

Is it possible to suppress the output of certain report elements when the report is printed or exported? I want a particular image element that has a hyperlink to be visible when the report is viewed within RS's ReportViewer, but don't want it to show up on the printed or exported file. I figure, if this is even possible, the most obvious methods would be either:

1. Put the element in the page header or footer, then set up the page header or footer to not show up on the printed or exported report.

2. Put the element anywhere on the report, and set it to hidden when the report is printed or exported.

Are either of these possible? If not, is there another way to accomplish this?

Thanks,
Don

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved