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

Similar Articles

[PageSpeed] 19

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:

XML Receipt format for online orders
Does anyone have the programming for the xml receipt that RMS needs when downloading orders? We already have our own webstore, and would like the orders to be downloadable directly into RMS. Thanks, Nick Here is a sample from MicroSoft: Good luck, Moe XML Sample of Downloaded Order The following XML text is a sample of a downloaded order from Yahoo Store. <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE OrderList (View Source for full doctype...)> <OrderList StoreAccountName="smspos"> <Order currency="USD" id="smspos...

Title & Menu bar does not show now..........
In moving a word doc with the mouse, I accidently moved it up too much. Now I cannot see the top half and cannot minimize or close the doc. I tried to click and hold, but it won't budge. Can anyone help me out? I can only close by using Task Mgr. If I open a new doc, the position is still the same. Try using Alt+F10 -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "Bdiva56" <Bdiva56@discussions.microsoft.com>...

emails never show up in Outlook
We have 3 users today that haven't been getting emails. Email sent to them can be seen on the email server, but after they pop the server it never shows up in outlook, but disappears from the server. One of the systems has email which gets stuck in the outbound folder, but the message has been sent and received. Can't delete the outgoing email from this folder. I have scanned these computers with latest anti-virus, but it did not indicate that virus was present. Found the issue. The computers apparently were infected and the anti-virus software corrupted the pst files. ...

Overlapping of values in different tables.
I am wondering if there is a way to create a pivot table - or some other way - that will show the relationships of rows between tables. I have attached data from potentially 4 different tables and the business is wanting to see the number of times that a paticular ID shows up in different table join combinations. For example, we want to know the count of unique IDs will show in the NPS, AAC and ECM tables. Is it possible to create a pivot table that will dynamically adjust based on a user choosing the column combinations that they want to see without having to create a separate pivot...

Shows message as Read after 5 seconds
Using Outlook 2003 Where is this setting located? ...

employee ID into text box, need to show all ID's that have ck in on same form
I have a problem, I enter employee ID into text box, I need to show all ID's that have ck in on same form. I have table with employee ID info. I need to show all employee's on the same form as they check in. I have done query's subforms showing the ID but will only refresh if I close the form then reopen. This is on a single PC. Using Access 2007 <I'm at a loss> I have; Employee_Info Table Date_Time_IN Query, Date_Time_IN Table, Date_Time_OUT Table jfire wrote: >I have a problem, I enter employee ID into text box, I need to show all ID's >...

Outlook not showing ALL sent Items...But shows some, why?
We are running Outlook 2003 clients against a Exchange 2003 server SP1, on Windows 2003 Working fine for all, except one user This user does not get 'sent items' populated consistently. We see items show up sometimes, or not at all or later. Yes, later, like 2 weeks later. And all items do not show up. I've built his account onto a different box from scratch, it populates all folders, and 'sent items' looks the same, so it is not Outlook 2003 settings. We do used Cached mode access for users. I am stumped on this. What could possibly cause this? "Phil&qu...

How do I display the lowest value in a row?
I want to show a spreadsheet that will search for the lowest value in the row and then display the column name at the end. It is for a price list so the name of the supplier will be accross the top, the product name down the left hand side. The formula will then review the values and select the cheapest then display the suppiers name next to the product name? Does anyone know if this can be done? Thanks for you help? Alex One way: Assuming Suppliers in B1:M1 and products in A2 and down, enter this formula in N2: =INDEX($B$1:$M$1,MATCH(MIN(B2:M2),B2:M2,0)) Copy N2 down with the fill ...

My data is not showing on the form.
I have a database with 3 tables in it. I created a form to input data. Whenever I input data, the records are stored to the tables but I cannot see the information on the form Teg, That's a bit confusing. Do you mean you enter a new record of information, and you see those entries on the form as you type, but after the record is saved...you can't find, or see, or go to, that record? Perhaps the DataEntry property of your form is set to YES. DataEntry = YES only allows users to enter new records, but they can not access any previously entered record. What makes m...

Create XML coforming to a XSD
Hello, I have data contained in different tables in the SQL Server database. Now I need to pull data from these tables (using a SP or a simple query) and then create a XML file that conforms to a particular XSD. I was just wondering if I could get any pointers on how I can use the .NET XML libraries to this. Any help will be appreciated. Thanks, Ganesh Hi Ganesh, The following article describes how you can use classes in the System.XML.Schema namespace of the Microsoft .NET Framework to build a tool that generates sample XML documents that conform to a given schema: http://msdn.micro...

show day in column B for Date in column A
I want the Excel to show the day of the week in column B for whatever date is entered in column A. Please advise. -- NYBoy ------------------------------------------------------------------------ NYBoy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=8360 View this thread: http://www.excelforum.com/showthread.php?threadid=530201 Here are 2 options: For a date in A1 B1: =TEXT(A1,"DDD") OR B1: =A1 Custom Format B1 to only show the day <Format><Cells><Number tab> Category: Custom Type: DDD Note: DDD shows the 3 letter day (Mon...

Need web addresses to show black in MS Publisher?
When an website address is typed in Publisher it shows and prints in blue. I need this to show and print in black. I have tried changing the font and font color without success. When this is printed with a black laser printer the print shows grey and I need it to show black throughout. Highlight the address, Insert, Hyperlink, click Remove Hyperlink. -- Mary Sauer http://msauer.mvps.org/ "churchlady" <churchlady@discussions.microsoft.com> wrote in message news:C440C4BE-96A5-4DBC-AB71-423E5CF23438@microsoft.com... > When an website address is typed i...

MS Money XML Report Format
Does anyone know where I can find a formal description of the XML report format Microsoft Money uses when you save a report as XML? I am thinking of an XML Schema or DTD, but a textual description would be good (or possibly even better) as well. I have had a look at a few examples from my own Money file and it doesn't look too complex. But as I want to parse these files in my own program, I would like to understand all the possible permutations and combinations which could be produced under a whole range of circumstances. I've googled the net to death but to no avail. Thanks i...

Evaluate String for Value from Table
I have a table [tbl_dc_dx] that contains a field for Primary Diagnosis but only a semi-colon delimited list of Secondary Diagnoses. The list is variable in length and the[sec_diag] codes are in no particular order. [tbl_dc_dx] sample data: Account sec_diag 1 5990;2851;6262;2809;6202 2 6262;2800;4019;2808;2469 3 2851;9100;9219;E8889;E8497 4 4111;2859;4019; 5 5855;42822;2724 6 25000;4019;4580;2801 A list of applicable diagnosis codes is available in a table called [tbl_dx_codes]. [tbl_dx_codes] sample data: diag_cd diag_desc 2800 280.0-CHR BLOOD LOSS ANEMIA 2801 280.1-IR...

Document Map showing
On one of my reports that I open via web server I get the preview display fine but it shows a "Document Map" section at the left that shows the ID number I am using as a link parameter in a subreport. How can I get rid of this when running the report? Thanks. -- David ...

Pivot Tables: Don't show data for detail, but still show subtotals
Hi there, Is it possible to show a pivot table with detail but have the data only show for the subtotals? I am trying to create a report that combines a staff list from HR to a budget list from Finance. It needs to show staff in post vs Budget. But the budget only applies to the department/Band combinations - not individuals. I have been asked to include names of people in the report so managers can see the breakdown of each person's WTE against the budget - here's an example of what I am thinking it might look like: Dept Band Position Name ...

How do you convert numbers as "text" to values for a long column .
I need to convert numbers as text to values that can be added for a long column of entries. HELP One quick way .. Select the entire col, say col A Click Data > Text to columns Click Finish -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>com ---- "geoexcel" <geoexcel@discussions.microsoft.com> wrote in message news:49D3D9DE-73B6-4D34-8BBE-E9886EC81411@microsoft.com... > I need to convert numbers as text to values that can be added for a long > column of entries. HELP =SUM(A2:A65536+0) If there are any non-number t...

Show in Groups #2
We recently converted our office from Outlook Express to Outlook. We have many users that do not like the Show In Groups feature. I know this can be turned off from View - Arrange By - Show In Groups. My question is can this be turned off for all email folders at the same time. Many of our users have quite a few folders that were transfered over from Outlook Express and want to turn this off for all folers. Thanks! Kathy - Lovullo <KathyLovullo@discussions.microsoft.com> wrote: > We recently converted our office from Outlook Express to Outlook. We > have many users that...

Macro to copy values then delete row for entire sheet
I need a macro that, when it identifies a given target phrase, will copy the cell above the target and then delete the entire row above the target. I would like it to do this for mulitple occurrences of the target phrase on the entire sheet. Please help. Thanks. Hi, So your macro will copy the cell and then delete the entire row. What happened to the copied cell stuff. If this helps, please click the Yes button. Cheers, Shane Devenshire "Pyrotoy" wrote: > I need a macro that, when it identifies a given target phrase, will copy the > cell above the target and then de...

Removing choices from combobox query but stilling showing for older entries
I have a form, frmMain that lists staff names in a combobox from a query into a lookup table. The table, lkpStaff has 3 fields, StaffID, Staff and ShowInViews. The RowSource Query for the combobox is: SELECT lkpStaff.ThingID, lkpStaff.Thing FROM lkpStaff WHERE (((lkpStaff.ShowInViews)=True)); The combobox ControlSource is the field Staff in tblMain which is populated from frmMain. By unchecking ShowInViews for staff that are no longer employed I can remove them from the combo's dropdown. However, of course, it also removes those staff names from older entries. I w...

Does Custom Autofiltering dates work in XML Lists?
I downloaded some reporting in XML format because it was the easiest. Now i am trying to use the autofilter to custom filter dates however it won't work! For instance, i'm trying to custom filter by: is less than or equal to 05/26/2006 ---- However it just won't work. Help? -- jim_0068 ------------------------------------------------------------------------ jim_0068's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32822 View this thread: http://www.excelforum.com/showthread.php?threadid=546057 Most likely your dates are seen as text, make su...

Making a time value permanent
I need a function or macro that will place the system time in cell A1 when the user presses enter at cell B1... and I want that time stamp to then "stick", i.e., never recalculate. Suggestions? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ In the worksheet code window, Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Not Application.Intersect(Target, Range("B1")) Is Nothing Then Range("A1").Value = Time End If End Sub Alan...

Storage Limits not being accepted or showing up in ESM
I am running Exchange 2003 with Service Pack 2 applied. I have taken off storage limits for a particular user and it still does not show up in ESM. Hi Eric, How long have you waited? It can take a couple of hours to take. "Sabo, Eric" <sabo_e@cup.edu> wrote in message news:65742EA8-D7A6-4D0F-853D-2BA091CB9E4C@microsoft.com... >I am running Exchange 2003 with Service Pack 2 applied. I have taken off > storage limits for a particular user and it still does not show up in ESM. > > ...

Can't get background on one page to show up
I read the previous question about backgrounds and it did not help me. I have publisher 2000. I am trying to insert a flower in the background. I have already gone to fill to make it gray. I tried inserting a rectangle and then I tried to put the grayed out flower in however when you click on the picture and send to background it goes behind the rectangle and onto EVERY PAGE. wilkenet <wilkenet@discussions.microsoft.com> was very recently heard to utter: > I read the previous question about backgrounds and it did not help > me. I have publisher 2000. I am trying to i...

Sent Email Now Showing
Items are not showing up in my sent email as of 3 days ago. It is set to 'Save items in sent folder'. any suggestions??? -- acreativepage You may have another PST file open. If you have folder view active, you can play around with that left side hierarchical view and see if there's another Personal Folders file there. That other Personal Folders may be where your Sent items are being saved. acreativepage wrote: > Items are not showing up in my sent email as of 3 days ago. It is set to > 'Save items in sent folder'. any suggestions??? > > > > &g...