Solved

Need a Powershell Parse of some XML code

Posted on 2014-03-10
5
162 Views
Last Modified: 2014-08-25
Code below work fine i move the data to the folders listed below. What I need The Select Query has a Meta_Data Column that gets parsed by the for each code a row gets created to each data set. What I would Like is to add the ID value from Query to each line but don't know syntax to do it


add-pssnapin sqlserverprovidersnapin100 -ErrorAction SilentlyContinue
add-pssnapin sqlservercmdletsnapin100 -ErrorAction SilentlyContinue
cls 

$DetailLines =  Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $SQLInstance -Query "SELECT top 10 META_DATA,ID FROM CKOLTP.dbo.P_MEMBER_HRA WITH (NOLOCK) WHERE META_DATA IS NOT NULL"

Foreach ($Line in $DetailLines )
{

#Write-Host $Line.META_DATA

[xml]$xml = $Line.META_DATA
$xml.ResultsCache | export-csv –append -NoType -Force "C:\Users\ltorres\Desktop\DataDumps_PS\test5.csv"
$xml.ResultsCache.Page | % { $page = $_; $_.Question | select `
  @{n='PageID'        ; e = {$page.ID}},
  @{n='flush_concepts'; e = {$page.flush_concepts}},
  @{n='valid'         ; e = {$page.valid}},
  @{n='update_date'   ; e = {$page.update_date}},
  ID, 
  Score, 
  Value
  
} | export-csv –append -NoType -Force "C:\Users\ltorres\Desktop\DataDumps_PS\test51.csv"  

}

Open in new window

0
Comment
Question by:Leo Torres
  • 2
  • 2
5 Comments
 
LVL 10

Expert Comment

by:JoeKlimis
ID: 39920132
Hi  ltorres321

Please can you provide a sample of your input data , and the result set , and then show the result you would like, as this is unclear how i can help you

Thanks
Joe
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39920941
Here is a sample the current code parse that data into several rows I need each of the rows created by the Meta data need to have the "ID" returned by the select query.
Sample2.html
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 40262016
Should you still be interested in a solution:

The output (of a single SQL row) is
PageID flush_concepts valid update_date          id  Score value
------ -------------- ----- -----------          --  ----- -----
1      0              1     7/25/2012 8:43:58 AM 1   0     1
1      0              1     7/25/2012 8:43:58 AM 2   0
1      0              1     7/25/2012 8:43:58 AM 3   0     3
1      0              1     7/25/2012 8:43:58 AM 4   0
1      0              1     7/25/2012 8:43:58 AM 5   0     2
1      0              1     7/25/2012 8:43:58 AM 6   0
1      0              1     7/25/2012 8:43:58 AM 7   0     1
1      0              1     7/25/2012 8:43:58 AM 8   0     jehovah's witness
1      0              1     7/25/2012 8:43:58 AM 9   0     65
1      0              1     7/25/2012 8:43:58 AM 10  0     2
1      0              1     7/25/2012 8:43:58 AM 11  0     210::505
1      0              1     7/25/2012 8:43:58 AM 12  0     70::365
1      0              1     7/25/2012 8:43:58 AM 13  0     2
1      0              1     7/25/2012 8:43:58 AM 14  0
1      0              1     7/25/2012 8:43:58 AM 15  0     1
2      0              1     7/25/2012 8:44:54 AM 16  0     30.1
2      0              1     7/25/2012 8:44:54 AM 17  2     3
2      0              1     7/25/2012 8:44:54 AM 18  0     1
2      0              1     7/25/2012 8:44:54 AM 19  0     2
2      0              1     7/25/2012 8:44:54 AM 20  0     1
2      0              1     7/25/2012 8:44:54 AM 21  0     1
2      0              1     7/25/2012 8:44:54 AM 22  0     1
2      0              1     7/25/2012 8:44:54 AM 23  0     2
2      0              1     7/25/2012 8:44:54 AM 24  0     2
3      0              1     7/25/2012 8:45:28 AM 25  0     4
3      0              1     7/25/2012 8:45:28 AM 26  0

Open in new window

which looks fine to me. The ID column is question.ID, Can you give an example of the desired output?
0
 
LVL 69

Accepted Solution

by:
Qlemo earned 500 total points
ID: 40262021
0
 
LVL 8

Author Closing Comment

by:Leo Torres
ID: 40282506
Sorry thought this was closed. Solution was provided
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A brief introduction to what I consider to be the best editor for PowerShell.
The following article is intended as a guide to using PowerShell as a more versatile and reliable form of application detection in SCCM.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question