show XML schema and values

I have an XML document that looks like the following:

<record>
 <Field1>value1</Field1>
 <Field2>value2</Field2>
 <Field3>value3</Field3>
</record>

What I'm aiming to do is to show the data in a report as in the following 
example:

record:
               Field1               value1
               Field2               value2
               Field3               value3

The "Field1-3"-elements can have any name, so I'm trying to get a table that 
has in one result column the element name, and in the second column the 
element value. How can I achieve this?

I'm primarely interested in the SQL script.
0
Utf
4/21/2010 12:20:01 PM
sqlserver.reportingsvcs 542 articles. 0 followers. Follow

2 Replies
718 Views

Similar Articles

[PageSpeed] 51

CJ
http://pratchev.blogspot.com/2007/06/shredding-xml-in-sql-server-2005.html


"CJ" <CJ@discussions.microsoft.com> wrote in message 
news:212B03EA-BADD-4D0E-A54D-762F6EE4B03C@microsoft.com...
>I have an XML document that looks like the following:
>
> <record>
> <Field1>value1</Field1>
> <Field2>value2</Field2>
> <Field3>value3</Field3>
> </record>
>
> What I'm aiming to do is to show the data in a report as in the following
> example:
>
> record:
>               Field1               value1
>               Field2               value2
>               Field3               value3
>
> The "Field1-3"-elements can have any name, so I'm trying to get a table 
> that
> has in one result column the element name, and in the second column the
> element value. How can I achieve this?
>
> I'm primarely interested in the SQL script. 


0
Uri
4/21/2010 1:37:44 PM
He only talks about getting the values of an XML document, not about getting 
the names. But I saw that I can use Xpath, so here is something that works 
for this specific case:



DECLARE @data AS XML
SET @data =
N’
<result>
<Field1>Value1</Field1>
<Field2>Value2</Field2>
<Field3>Value3</Field3>
</result>’

SELECT
result.data.query(‘local-name(.)’) AS ResultName,
resultfield.data.query(‘local-name(.)’).value(‘.’, ‘VARCHAR(MAX)’) AS 
FieldName,
resultfield.data.query(‘.’).value(‘.’, ‘VARCHAR(MAX)’) AS FieldValue,
FROM @data.nodes(‘result/.’) AS result(data)
	CROSS JOIN @data.nodes(‘result/*’) AS resultfield(data)


And there we are. Using a CROSS JOIN didn't give me any wrong results, so I 
guess I can go with that for now.



"Uri Dimant" wrote:

> CJ
> http://pratchev.blogspot.com/2007/06/shredding-xml-in-sql-server-2005.html
> 
> 
> "CJ" <CJ@discussions.microsoft.com> wrote in message 
> news:212B03EA-BADD-4D0E-A54D-762F6EE4B03C@microsoft.com...
> >I have an XML document that looks like the following:
> >
> > <record>
> > <Field1>value1</Field1>
> > <Field2>value2</Field2>
> > <Field3>value3</Field3>
> > </record>
> >
> > What I'm aiming to do is to show the data in a report as in the following
> > example:
> >
> > record:
> >               Field1               value1
> >               Field2               value2
> >               Field3               value3
> >
> > The "Field1-3"-elements can have any name, so I'm trying to get a table 
> > that
> > has in one result column the element name, and in the second column the
> > element value. How can I achieve this?
> >
> > I'm primarely interested in the SQL script. 
> 
> 
> .
> 
0
Utf
4/22/2010 6:43:01 AM
Reply:

Similar Artilces:

Outlook shows offline during dial up
Having a problem with outlook 2002. Over the WAN everything works fine. Dial up and connect to the network (VPN) internet work fine. Outlook will sync up however, a red x shows up in the bottom right hand corner. Outlook shows offline, I can send and receive but must hit send and receive. I have reloaded the computer from scratch and still have the same problem. Could it be a Excange setting? Any Ideas? Thanks for the help, If you hit File | Connect to Exchange Server what happens? -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP http://home.hawaii.rr.com/schorr **I apologize ...

Print values of a listbox
Is there a way to make a report that prints the values of a form's listbox. I tried making a report in design view to accomplish this. I inserted a listbox in the report and made it bound to the form's listbox, but the report only prints a limited amount of items on the list (those which are visible in the listbox). Is there a way to have the report print all the values of the form's listbox? How is the list box populated? If it's based on a table or query, make a report based on that table or query. If the Row Source Type is a Value List, I don't know.... -- Jerry...

help formula versus value
Hi, I�ve got a problem. I get streaming forex data in cell i2 whenever the data changes I want to trigger a script to check something. I used this script in vb worksheet to trigger it. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("i2")) Is Nothing Then bla bla bla ect. But it only reacts when the actual value changes like when I type something in cell i2. Since there is a formula in i2 to get the streaming data it won�t trigger it. What am I doing wrong?:confused: Thanks for helping -- climax -----------------------------------------...

analytical accounting lookups should only show valid codes
When entering AA transaction distributions, the code lookup window should only show valid code combinations. It's not intuitive for the user to have to select a code and get an error message to discover which codes are valid. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the mess...

GetObjectData not firing using XML Serialization
What attributes/code do I need to add to a class to get GetObjectData to fire? I need to use XML serialization to serialize an object with private members including complex data structures which eventually may contain other private objects. I can get that method to fire using SOAP serialization, but shouldn't I be able to customize my serialization process using XML serialization? Thanks, Todd. ...

Revert picklist back to original value...javascript
Hi, I have a picklist that, when certain values are selected, will populate the values of other fields. When this happens, a warning pops up telling them that this behavior will happen and the user can pick OK or CANCEL. When the hit CANCEL, it still selects the new picklist value they just selected. Is there a way to have it go back to what was previously selected?? Any help greatly appreciated! Thanks! On Jul 25, 8:00 pm, @VGroupby1 <VGroup...@discussions.microsoft.com> wrote: > Hi, > > I have a picklist that, when certain values are selected, will populate the > ...

Show a blank result in a cell when there is no value in the "Lookup" cell
I apologize if this question has been asked, but I have been unable to find an answer searching the topics. I'm using the following formula in cell C3: =INDEX(LastName,MATCH(A3,EmpID,0)) When I type in an employee ID in A3, his/her last name shows in C3. However, when there is no value in A3, C3 shows error "#N/A". Is there a way to show a blank cell in C3 until a value is entered into A3? Thanks in advance! Mike On Aug 5, 10:45 am, "Michael Slater" <mslater...@comcast.net> wrote: > I'm using the following formula in cell C3: > =INDEX(LastNam...

Show Window
I am currently developing a MFC SDI application. I want to integrate a tray icon with it. When the application is minimized it hides by ShowWindow(SW_HIDE) and then when double clicked on tray icon shown by ShowWindow(SW_SHOWNORMAL). But the problem is if some other application is activated after my application is minimized, ShowWindow shows it but it's behind some screen. I have tried with SetFocus didn't work. Please help me to solve this problem. Thank you. Varuna Try BringWindowToTop. ---- Ajay Kalra ajaykalra@yahoo.com In addition to Ajay's idea you can also use SetFor...

loading XML into a dataset
I'm currently reading data from an XML file using XPathNavigator and XPathNodeIterator to select the subset of data that I want. I've never using the XPath objects before so I'm not sure how to get this data into a dataset. XPathDocument doc = new XPathDocument(@"C:\inetpub\wwwroot\test\test.xml"); XPathNavigator nav = doc.CreateNavigator(); XPathNodeIterator iter = nav.Select("/test/translation/engword[preceding-sibling::fraword = '" + strFraWord + "']"); Can anybody help me out with this? Any assistance would be re...

Information Stores show dismounted yet mail is still flowing...
Background: Windows AD Mixed moded. Exchange 2003 installed on to a 2 node Microsoft cluster. 1 Front End server...connecting to the primary cluster node. Windows 2003 Standard on, FE Windows 2003 Enterprise Server on BEs (<---cluster) no service packs on either. Exchange has SP1 installed on both nodes and the FE. There are still Exchange 5.5 servers in the environment although they are not being used. The Issue: .....the information stores and public folders all show that they are dismounted...yet email is still flowing into and out of the server without any issues. I just...

reminder still shows up
when I run Outlook.exe /cleanreminders it simply opens Outlook, I am running Vista "Toppro" <Toppro@discussions.microsoft.com> wrote in message news:7AE8DBF7-6BE3-454A-82F9-5A040566026C@microsoft.com... > when I run Outlook.exe /cleanreminders it simply opens Outlook, I am > running > Vista Outlook version? The Windows version isn't as important and that of Outlook. -- Brian Tillman [MVP-Outlook] ...

Trouble Reading XML string
I have a method that generates an string in XML format and passes it to be processed. The string is in the following format: <grandparent> <parent> <child1>text</child1> <child2>text again</child2> </parent> <aunt> <cousin1>text</cousin1> <cousin2>text2</cousin2> <cousin3>text4</cousin3> </aunt> </grandparent> string xmlInput = <values above> XPathDocument xpDoc = new XPathDocument(new StringReader(xmlInput)); XPathNavigator xpNav = xpD...

Results show as a minus. WHY?
I have an application that was developed by another developer using VB.net which reside in a SQL table. I need to use some of the data from this table, however the data shows as -0.5 or - 0.99 or -1.70 etc in my Access tables. How can I get those numbers to show as a percentage with only one decimal point to the left? Example: 3.6% or 31.4 %. Any help would be greatly appreciated. No matter what I do it shows that dang "-" at the beginning. You can use the Abs function to strip off the negative Abs([YourTable].[YourField]) And you can format that as percentage either using th...

Cell value in formula
Does anybody know if I can report a value in a cell to formula in a another cell. For exampel if the value in A1 is "Sheet2" and a formula in A2 is =LETARAD(B2;'((A1))'!A:B;2;FALSKT) Im not sure what 'Letarad' is called in english, Findrow? /Marcus Hi have a look at the INDIRECT function for this >-----Original Message----- >Does anybody know if I can report a value in a cell to formula in a another cell. > >For exampel if the value in A1 is "Sheet2" >and a formula in A2 is >=LETARAD(B2;'((A1))'!A:B;2;FALSKT) > >Im not su...

Concatenating cell values to create sheet names
I would like to concatenate cell values to create a name that corresponds to a worksheet name and then use that in a formula. Does anyone know how to do this? You can use: =a1&a2 to concatenate a couple of cells. To refer to that in another formula, you'd need something like: =indirect("'" & a1&a2 &"'!A1") or if there's a chance your concatenated string refers to a non-existing worksheet and you want to avoid a #ref! error, you can check first: =IF(ISERROR(CELL("address",INDIRECT("'"&A1&A2&"&#...

Using ajax call to fetch multiple results from multiple queries and showing them 1 by 1 as the results comes.
Hello, I've 20 labels in a 2x10 table on a page. for each label, data comes by individual queries. (total 20 queries for the page.) and it takes about 30-40 seconds for the page to load, and it's not comfortable for the user to see blank page. I want that each cell will show loading.gif images. As and when data comes from query, the images should go visible=false, and the label should display the values one by one. How do I do this task using ajax? Some ASP.NET code example will be helpful for me, as I'm a kind of beginner. Thanks ...

show comments when cell is highlighted Vs when cursor runs over? #2
How do you make the comments appear when the cell is highlighted opposed to when the mouse cursor runs over the cell? You can press Shift/F2, or Insert/Edit Comment. If you wanted to see it "automatically", when you select the cell, you'd need a macro, but I don't think this is what you're asking. Bob Umlas Excel MVP "Don" wrote: > How do you make the comments appear when the cell is highlighted opposed to > when the mouse cursor runs over the cell? To at least Excel 2000, if you have the Tools, Options, Comment Indicator Only you will see the co...

Report Server shows 'Service Unavailable'
Hi, before install, the SRS setup, send me a warning, some like "the Windows Sharepoint Services are install and not permit initialize a Reportserver component" i skip wiht the installation, so.. I can't run http://<localhost>/Reports and http://<localhost>ReportServer show a message "Service Unavailabe", but the ReportServer service is running How initialize that or what do i do? ------=_NextPart_0001_09E699C9 Content-Type: text/plain Content-Transfer-Encoding: 7bit <Miguel@discussions.microsoft.com> wrote: > before install, the SRS set...

Comments not showing
When a SharePoint workflow is edited and a comment is added. The comment does not display in the workflow status screen under workflow history. It shows as follows: Task assigned to Andre Wessels was completed by System Account. Comments: Instead of: Task assigned to Andre Wessels was completed by System Account. Comments: Please update to the new company logo. This happens for all standard SharePoint workflows (Approval, Collect Feedback). Any custom workflows does show the comment. The comment field is update in the task item. It just does not show. There is no error in t...

Reverse fold change values
Hi Sorry about the form of this question, I'm not very good at explaining things!! I have compared values in conditionA against condition B. It is a unidirectional comparison in that I cannot compare condition B against conditionA. Say that Condition A has a value of 10 and condition B has a value of 2, I will get a fodl change value of 5, in that Condition A is 5 fold greater than conditionB. However if condition A has a value of 2 and conditionB has a value of 10, then I get a fold value of 0.2, even though the fold change is the same, it's just the direction of the fold cha...

Labels on Chart with Negative Value Axis
I have a question about an Excel chart. I have a series of graphs which compare a number of factors relating to "card swipe" data. This relates to data plotting on an X (horizontal) and Y (vertical) axis graph. The X axis represents the month of the year. The Y axis is the "data error rate." Data is stored for each successful and each unsuccessful "swipe." Successful swipes are positive valued while unsuccessful are negative. In some cases all of the values are negative. When these values are plotted Excel inverts the graph the graph and places the labels into t...

how to show numbers that begin with 0 in excel
I need to type numbers into an excel spreadsheet some of them begin with a zero some do not. How can I have excel show the 0 in the numbers that begin with 0. If I type 0236547 the number shows as 236547. I don't want all the numbers I type to begin with zero. I am using Excel 2003. Thanks very much for your help. Best regards, Dee use a custom format with how ever many digits you need, 000000 -- Gary Keramidas Excel 2003 "Dee" <Dee@discussions.microsoft.com> wrote in message news:EA60FC8A-C7CB-432A-B2B2-EEEBF86D2646@microsoft.com... >...

how to query a dataset with xml tables inside.
This is a multi-part message in MIME format. ------=_NextPart_000_0030_01C3DB37.4468A9E0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have two xml docs, authors & articles. Both have authorID as a common = node <?xml version=3D"1.0" encoding=3D"utf-8"?> <!--This is a comment--> <Authors> <Author> <AuthorID>0</AuthorID> <Name>Mark</Name> <Login>mark</Login> <Pwd>markpwd</Pwd> </Author> <Author> <AuthorI...

Relationships, duplicate values
Hi, I'm getting the common message "The changes you requested to the table were not successful because they would create duplicate values...." I think my table relationships are causing this and don't know how to get around. I have 4 tables: tblCustomer, tblWorkOrder, tblClientBuildings and tblClientBuildContacts, the last three are related to tblCustomer by CustomerName(Primary key in tblCustomer). I have a form frmCustomer that has a subform that is a continuous form that is bound to tblClientBuilding. On this subform there's a command button that opens a pop...

Show more time in weekly view?
Outlook 2007 On default the weekly view is only showing until 4 PM without scrolling down. I know there is a way to let you see into the evening hours also without scrolling. Help please. Thanks... Bob ...