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


ADVERTISEMENT

Transact SQL :: FOR XML To Write Data With Carriage Return / Line Feed At End Of Elements?

Sep 30, 2015

I am trying to use FOR XML under SQL Server 2014 to write out a large XML data set. I want it to look like

<CVS_Member_Add_Change>
    <RecordType>3</RecordType>
    <Carrier>1266</Carrier>
    <MultiBirthCode>0000000</MultiBirthCode>
    <MemberType></MemberType>

[Code] ....

That's how it looks when you click on the results of a small subset of the query.  Just what I want.  Unfortunately when you try to right click and save it you get 

<dataroot><CVS_Member_Add_Change><RecordType>3</RecordType><Carrier>1266</Carrier<MultiBirthCode>0000000</MultiBirthCode><MemberType></MemberType<LanguageCode>1</LanguageCode><DURFlag></DURFlag><DURKey></DURKey><SocialSecurityNumber>000000000</SocialSecurityNumber</CVS_Member_Add_Change>

Everything being on one line blows up the translator application that reads the data.

The FOR XML statement copied out of the query is below.

FOR XML RAW ('CVS_Member_Add_Change'), ROOT('dataroot'), ELEMENTS 
GO

Is there a way in the T-SQL to force it to break lines neatly?

Is there a way to force it to a specific file name or directory?

View 3 Replies View Related

Transact SQL :: Parse Unknown Number Of Data Elements To Multiple Lines

Jun 11, 2015

We are using a table that may give 1 to and unknown number of data elements (ie. years) .   How can we break this to show only three years in each row.  Since we don't know the number years we really won't know the number of rows needed.  Years are stored in their own table by line.  
 
car make year1 year2 year3
A   volare 1995 1996 1997
a   volare 1997   1998   1999
b toyat  1965    1966   1968

We can pivot out the first X# but we don't know how many lines so we don't know how many rows we will be creating.

View 8 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

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

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 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

Modify Report Elements At Run Time

Apr 18, 2007

I have a report that is embedded in a .net 2.0 web form. It works. I would like to be able to modify some of the elements in the report as the web form loads. I have tried the following code that did NOT work:



Dim myImage1 As New Image

myImage1 = Me.ReportViewer1.FindControl("Image1")

myImage1.ImageUrl = "Images/small_info_logo.jpg"



Does anyone know of a site that has some godo examples and object models on how to do this?

Thanks

Chuck

View 4 Replies View Related

Missing Elements In Report Manager

Nov 30, 2005

Has anyone run across this before? I'm missing the Contents and Properties tab along with the button bar containing the "New Folder", "New Data Source", "Upload File" and "Show Details" buttons in the Report Manager. Also, which I think may be related, when I attempt to deploy a report I get the error: "A connection could not be made to the report server http://MyServer/Reports.

View 4 Replies View Related

Disappearing Data Elements In The Header

Apr 4, 2007

I have created a number of reports where I need to display database elements in the Header record. I have been using the reportitems feature to get them to display there. When I preview the report the header looks fine; however, when the report is exported to pdf or printed out the header information completly disappears.



Any suggestions would be great.

View 2 Replies View Related

OpenXML Elements Case Sensitivity Problem

Mar 19, 2007

Hi All - I am using OpenXML to insert data into SQL Server 2005. All works okay except for the fact that in my XML I have an attribute tag and elements as follows.<Location>    <Place City = "LA" State = "CA">    <Place city = "NY" State = "NY">    <Place City = "St. Louis" state = "MO"></Location>  If you notice in the above code,  the 2nd city NY has element tag "city" in lower case and others are "City" with a Upper Case "C" same for state in case of MO which is lower case and others are Upper Cases..Now I understand that XML is case sensitive. What should I do if I have XML files in the above format with inconsistent element tags? Because if I run the below code.. using OpenXML...Insert Into TableXSelect *FROM OPENXML (@DocHandle, '/Location/Place', 1)                             WITH (City varchar(255) '@City', State varchar(255) '@State') ox Then in the above scenario it won't insert values for city "NY" and state "MO" since both have lower case elements tags and in my OpenXML I have specified as @City and @State with Upper Case "C" and "S" respectively.  Now in such a scenario what can one do? Any thoughts is to how I can also grab value for lower case "city" and "state" in the above XML.  Any help would me much appreciated. Thanks a billion.Regards- web

View 2 Replies View Related

Setting BackgroundColor Of Report Elements In SSRS

Jan 9, 2008



Has anyone seen the error:


[rsInvalidColor] The value of the BackgroundColor property for the textbox €˜textbox14€™ is €œTransparent€?, which is not a valid BackgroundColor.


This is appearing when I set the BackgroundColor property in a SSRS report using an expression. What I'm trying to do is have the colour of the text and background returned by the stored procedure that returns the data for use in Matrix and Table. But the problem seems to relate to all controls in SSRS 2005.

Steps to reproduce are create a new report, add a TextBox.

Go to properties of the textbox and select the Backgroundcolor and select Expression...

Then add the expression:

="Transparent"

Transparent doesn't seem to be a constant, but does appear to be a value you are allowed to selected (and is the default) from the drop down in the background colour.

The work-around is to use White, which works if you don't use a background image or change the background colour of the page.

However if you do us the "Transaparent" colour, then it does return the correct effect, but generates an error in the Output window within the development environment. I'm just ignoring the error for the time being. but has anyone else seen this error.

Andy

View 7 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

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

Split One Row Into Multiple Rows Based On Time Elements

Feb 5, 2007

I'm dealing with a problem.

The record information example

DateTimeStart , DateTimeEnd , action , duration (seconds)
2007-02-02 10:30:22 , 2007-02-02 11:30:22 action1 , 600

what i want is for every half hour between start and end a record

10.30 action1
11.00 action1
11.30 action1

how can i create this, i'm a little stuck on this

View 2 Replies View Related

SQL Server 2012 :: Recursive Concatenation Of Parent Elements

Jul 28, 2015

I have a hierarchical structure for mapping products to categories, categories go 3 levels deep (depth is defined in articlegroups.catlevel, 0 being the main category and traversing down to lower category level 2). Also, a product may be in more than 1 category(!).

product details are stored in `[products]`
articlegroups are defined in `[articlegroups]`
and the mapping of the products to the articlegroups are defined in `[products_category_mapping]`

Now, I want to retrieve index the full category path for each item, so with the data provided below, I'd expect these 2 rows as a result:

id categorystring
2481446 Taarttoppers > Taarttoppers grap'pig
2481446 Bruidstaart > Taarttoppers > Grappig

Now I can get the separate fields via a statement like this:

SELECT ga.slug_nl as slug_nl_0
FROM articlegroups ga
INNER JOIN products_category_mapping pcm ON pcm.articlegroup_id=ga.id
INNER JOIN products gp on gp.id=pcm.artikelid
WHERE gp.id=2481446

[code]....

View 9 Replies View Related

Problem Getting Select Data Set For XML Auto, Elements Into A Table Variable

May 26, 2004

I have a simple select quesry but with 'for XML AUTO, ELEMENTS'. I want to put in the resulting xml string into a temporary table and then alter that string as per my requirements. But I am unable to put this XML string into a table variable. Please offer your suggestions.

View 4 Replies View Related

T-SQL (SS2K8) :: Extract Quoted Text Elements From Varchar Column

Nov 18, 2014

I need to extract specific text elements from a varchar column. There are three keywords in any given string: "wfTask," "wfStatus" and "displayReportFromWorkflow." "wfTask" and "wfStatus" can appear multiple times, but always as a pair and will each be followed by by "==" (with or without surrounding spaces). "displayReportFromWorkflow" is always followed by "(" and there can be spaces on either side. The text elements will be between a pair of double quotes, and following one of keywords. For each row, I need to return the task, status and report name.

declare @t table (rowID int, textValue varchar(1024))
insert @t
(rowID, textValue)
values

[Code] ....

Output:
rowID, Task, Status, ReportName
----- --------- ------- ------------------------
1, Issuance, Issued, General Permit
2, Issuance, Issued, Capacity Letter Type III
2, Review, Denied, Capacity Letter Type III

I started with a string splitter using the double quote character, referencing elements "i" and "i+1" where the text like '%wfTask%' or '%wfStatus%' or '%displayReportFromWorkflow%', but the case of multiple task/status in a row has confounded me so far.

Unfortunately, CLR is not an option.

View 1 Replies View Related

How To Reference Dataflow Elements In A SQL Statement Embedded In A Lookup Transformation?

May 8, 2008



Hi guys,

I need to use a SQL statement to lookup a value from a SQL server database table that relates to a column in my dataflow.

Imagine a SQL database table called 'cars' with values of

Year | Description
2005 Ferrari 355
2005 Ferrari 355 Spider
2006 Ferrari 355 F1

In my data flow I have Year and Model eg.

Year | Model
2005 355
2006 355


In my SQL statement I want to select from the 'cars' table where the years match exactly but the 'description' is like the 'Model'. eg. %355%

So essentially, how do I construct the 'like' clause in the select statement to reference the 'Model' column please?

thanks for your help,

Chris

View 4 Replies View Related

SSIS - Handling Recursive XML Elements In Data Flow Task

May 22, 2008

Hi All,
I have a requirement here to import data from XML file to SQL Database. The XML schema contains of various elements and one of the element is recursive ie. Parameter node contains parameter node within it and it can have n number of iterations. I have given the sample schema below:


<xs:element minOccurs="0" name="Parameter">

<xs:complexType>

<xs: sequence>

<xs:element minOccurs="0" name="ID" type="xs: string" />

<xs:element minOccurs="0" name="Description" type="xs: string" />

<xs:element minOccurs="0" name="Type" type="xs: string" />


<xs:element minOccurs="0" name="Parameter">
<xs:complexType>

<xs: sequence>

<xs:element minOccurs="0" name="ID" type="xs: string" />

<xs:element minOccurs="0" name="Description" type="xs: string" />

<xs:element minOccurs="0" name="Type" type="xs: string" />



<xs:element minOccurs="0" name="Parameter">
<xs:complexType>

<xs: sequence>

<xs:element minOccurs="0" name="ID" type="xs: string" />

<xs:element minOccurs="0" name="Description" type="xs: string" />

<xs:element minOccurs="0" name="Type" type="xs: string" />

</xs: sequence>

</xs:complexType>

</xs:element>


<xs:element minOccurs="0" name="Parameter">
...............

</xs: sequence>

</xs:complexType>

</xs:element>

</xs: sequence>

</xs:complexType>

</xs:element>

But all the nodes contain the data which has to be imported to a single table dbo.Parameters. I cannot use Union ALL since i dont know how many iterations I will have in the file. Is there any way to do this operation in Data Flow Task using XML Source? Can anyone help me on this?

Thanks,
Dhileep

View 1 Replies View Related

Single Configuration File - Mulitple Packages - Elements Not Used In Each Package

Dec 18, 2007

Hi,

I'm fairly new to the SSIS world, and I've recently ported a bunch of dts packages over to SSIS. I'm an ASP.NET developer so I'm very familiar with the capabilities that configuration files give you, and I attempted to set up my solution as follows:

All of my "Data Sources" are at the project level, and added (with the same name) to each package. I wanted to have a single config file that had all of the project-level settings (i.e. connection strings, data file paths, etc). I then have a config for each package with the package level settings - i.e. variables, etc.

The problem becomes that all packages do not use all data sources. This results in an error when I try to open up a package for editing, it complains that it doesn't have a reference to data source XYZ that it is seeing in the configuration file.

Is there any way that I can get around this? If I have a password to a database change, I don't want to have to look through every config file and change it in multiple places.

View 4 Replies View Related

Xslt Filters On Xml Output. Cdata-section-elements And Omit-xml-declaration Problems.

Sep 14, 2006

Hi All,



I'm using some xslt documents to transform the xml output of my Reports
but have come across two curiosities where the xslt filter seems to
behave unusually.



Firstly, I need the final saved file to have an xml declaration, which
I believe it should do by default. Even if I put
omit-xml-declaration="no" in the xsl:output tag I don't get an xml
declaration. At present we have a custom job that writes these
declarations back into the xml after SRS has saved it.



Secondly and more importantly, I need to have some of my output tags
wrapped in CDATA sections. I've tried using the cdata-section-elements
attribute, again with no luck.

my XSLT looks something like this (simplified for space)



<?xml version="1.0" encoding="utf-8"?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output method="xml" encoding="utf-8" media-type="text/xml" omit-xml-declaration="no" cdata-section-elements="description"/>

<xsl:template match="/">

<xsl:for-each select="Report/table1/Detail_Collection/Detail">

<item>

<description>


<xsl:value-of select="@Description"/>

</description>

</item>

</xsl:for-each>

</xsl:template>

</xsl:stylesheet>



The output is something like:



<item>

<description>My first description text...</description>

</item>


<item>


<description>My seconddescription text...</description>


</item>



What I want is:

<?xml version="1.0" encoding="utf-8"?>

<item>


<description><![CDATA[My first description text...]]></description>


</item>



<item>



<description><![CDATA[My secondfirst description text...]]></description>



</item>



All help gratefully appreciated.



Thanks - Andrew.

View 5 Replies View Related

Dynamic Data Elements For A Data Collection Application

Sep 20, 2005

What is the better table design for a data collection application.1. Vertical model (pk, attributeName, AttributeValue)2. Custom columns (pk, custom1, custom2, custom3...custom50)Since the data elements collected may change year over year, whichmodel better takes of this column dynamicness

View 7 Replies View Related

[XML Task] Error: An Error Occurred With The Following Error Message: There Are Multiple Root Elements.

Aug 18, 2006

I'm trying to use an XML Task to do a simple XSLT operation, but it fails with this error message:

[XML Task] Error: An error occurred with the following error message: "There are multiple root elements. Line 5, position 2.".

The source XML file validates fine and I've successfully used it as the XML Source in a data flow task to load some SQL Server tables. It has very few line breaks, so the first 5 lines are pretty long: almost 4000 characters, including 34 start-tags, 19 end-tags, and 2 empty element tags. Here's the very beginning of it:

<?xml version="1.0" encoding="UTF-8"?>
<ESDU releaselevel="2006-02" createdate="26 May 2006"><package id="1" title="_standard" shorttitle="_standard" filename="pk_stan" supplementdate="01/05/2005" supplementlevel="1"><abstract><![CDATA[This package contains the standard ESDU Series.]]></abstract>

There is only 1 ESDU root element and only 1 package element.

Of course, the XSLT stylesheet is also an XML document in its own right. I specify it directly in the XML Task:

<?xml version="1.0" encoding="UTF-8"?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"/>

<xsl:template name="identity" match="@*|node()">
<xsl:copy>
<xsl:apply-templates select="@*|node()"/>
</xsl:copy>
</xsl:template>

<xsl:template match="kw">
<xsl:copy>
<xsl:apply-templates select="@*"/>
<xsl:attribute name="ihs_cats_seq" select="position()"/>
<xsl:apply-templates select="node()"/>
</xsl:copy>
</xsl:template>

</xsl:stylesheet>


Its 5th line is the first xsl:template element.

What is going on here? I do not see multiple root elements in either the XML document or the XSLT stylesheet.

Thanks!

View 5 Replies View Related

Transact SQL

Jan 7, 2008

I have the following SELECT statement. It returns the first record from EVENT_TEACHERS. I want the last record. 'iID' is the unique identifier so I want to select this stuff where iID is max. How can I add this to my string? I can't get the block out of my head of wanting to put the aggregate in the WHERE clause.
str = "SELECT vwStaffList4Assessment.*, EVENT_TEACHERS.iID, EVENT_TEACHERS.iAsmtID, ASMT.* FROM EVENT_TEACHERS INNER JOIN ASMT ON EVENT_TEACHERS.iAsmtID = ASMT.iAsmtID INNER JOIN vwStaffList4Assessment ON ASMT.DVN=vwStaffList4Assessment.DVN"
Thanks

View 5 Replies View Related







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