XML FOR SQL query question

Toggle sidebar Toggle sidebar
Homerboy

Homerboy

Lifer
Mar 1, 2000
30,890
5,001
126
I'm trying to learn SQL and XML (more or less at the same time) and I am stuck on a query at this point.


the end of my query looks as so:


FOR XML PATH('DebtRecord'), TYPE, ELEMENTS, ROOT

and technically the results are OK. creating 1 <root> which encloses multiple <DebtRecord> values all at the same level.

However in the XML format I have to match, between the <root> level and the <DebtRecord> there is another value <TRIPHeader> that is of the same level as <DebtRecord>... however there will be only 1 <TRIPHeader> value, compared to 100s of <DebtRecords>. Does that make sense?


I can not figure out how to get that single <TRIPHeader> to populate in my results file at all.
 
O

Oyster

Member
Nov 20, 2008
151
0
0
I'd suggest when it comes to "complex" SQL questions, you may get more responses if you post your table creation scripts with some sample data.
 
Leros

Leros

Lifer
Jul 11, 2004
21,867
7
81
Just curious about what you're doing. Are you using SQL statements to extract data from XML? If so, that's pretty cool. What library are you using?
 
O

Oyster

Member
Nov 20, 2008
151
0
0
He's using SQL Server. Has native support for XQuery and XPath. Oracle has XML DB with similar features.
 
Homerboy

Homerboy

Lifer
Mar 1, 2000
30,890
5,001
126
Leros said:
Just curious about what you're doing. Are you using SQL statements to extract data from XML? If so, that's pretty cool. What library are you using?
Click to expand...

I'm dumping (or attempting to dump) from MSSQL to XML.

As for an example, I'm having a hard time getting a post here to retain the formatting hierarchy of the XML. <code> and <html> doesn't seem to work too well.... maybe a snapshot?
 
O

Oyster

Member
Nov 20, 2008
151
0
0
Damn, Homer - all those good looks do not seem to be helping you much :).

Try the code tags or pastebin.com.
 
Leros

Leros

Lifer
Jul 11, 2004
21,867
7
81
I would just do a query to get all the data and then pass it into a template to get the XML. You could batch it up if you have a lot of data. You could code that up in about 20 lines of code and a pretty simple template. Shouldn't take more than 10-20 mintues.
 
Homerboy

Homerboy

Lifer
Mar 1, 2000
30,890
5,001
126
Oyster said:
Damn, Homer - all those good looks do not seem to be helping you much :).

Try the code tags or pastebin.com.
Click to expand...

LOL yeah my good looks failed me this time around.
I will look over at pastebin.com and/or try to get an example up here.


For now, I have resorted to the thought of just copying and pasting that <TRIPHeader> portion into the final XML, but for some reason, when I open my SQL generated XML file, it puts it all in 1 single line of text... making it kind of hard. (if I open in a web browser or XML viewer it formats properly, but I can't edit...)
 
O

Oyster

Member
Nov 20, 2008
151
0
0
If you have Visual Studio or Eclipse, copy the entire XML blob and paste it in Visual Studio or Eclipse. Either IDE will reformat the data and show you the XML the way it should be shown.
 
Homerboy

Homerboy

Lifer
Mar 1, 2000
30,890
5,001
126
Actually I think I have figured out a work around using a VB script within SSIS to combine the two queries into 1 final .xml file.

HOWEVER, now I am faced with a new hurdle.... how the H do you convert Gregorian date to Julian day in a MSSQL query?
 
Homerboy

Homerboy

Lifer
Mar 1, 2000
30,890
5,001
126
Ok I'm going to change this question up a bit, and provide some examples of queries...

I have 2 queries that are XML FOR SQL

The 1st query is as follows:

Code: 
SELECT 
CONVERT(VARCHAR(23), GETDATE(), 126) AS 'FileCreationTimestamp',
'AGENCY NAME GOES HERE' AS 'Tranmittername',
'396005532' AS 'SubmissionID',
dbo.ConvertGregorianToJulianDate(GETDATE()) AS 'SubmissionID',
DATEPART(HH,GETDATE()) AS 'SubmissionID',
DATEPART(MI,GETDATE()) AS 'SubmissionID',
'Test' AS 'ProcessType'

FOR XML PATH('TRIPHeader'), TYPE, ELEMENTS

This provides me with a nice XML output formatted properly with single <TRIPHeader> being the topmost element and all subsequent elements contained within it.


The second query is:

Code: 
SELECT DISTINCT

CASE WHEN (DIARYINT.CODE = '383') THEN 'New' END AS 'TypeTransaction',
CASE WHEN (DIARYINT.CODE = '385') THEN 'Change' END AS 'TypeTransaction',
Replace(USER.SSN, '-', '') AS 'Individual/SSN',
Replace(USER.DL, '-', '') AS 'Individual/DLN',

D1_NAME AS 'Individual/payerName/FirstName', 
USER.STREET AS 'AgencyUSERAddress/AddressLine1',
USER.CITY AS 'AgencyUSERAddress/City',
USER.ST AS 'AgencyUSERAddress/State',
Replace(USER.ZIP, '-', '') AS 'AgencyUSERAddress/ZIPCode',
DIARYINT.FILENO AS 'AgencyInternalDebtNumber',
MASTER.BALANCE AS 'AgencyDebtBalance',

'934' AS 'AgencyNumber',
'034' AS 'AgencySubunitNumber'


FROM         MASTER INNER JOIN
                      USER INNER JOIN
                      DIARYINT ON USER.FILENO = DIARYINT.FILENO INNER JOIN
                      INFINITY ON DIARYINT.FILENO = INFINITY.FILENO ON MASTER.FILENO = INFINITY.FILENO

WHERE (DIARYINT.CODE = '385' AND DIARYINT.SOURCE = 'DATA') OR  (DIARYINT.CODE = '383' AND DIARYINT.SOURCE = 'DATA')
FOR XML PATH('DebtRecord'), TYPE, ELEMENTS


This provides me with a nice XML output formatted properly with <DebtRecord> as the topmost element and all subsequent elements contained within it (there are obviously multiple <DebtRecord> elements returned, as many as are found by the WHERE condition

What I WANT however, is the two queries to be combined.... so I get the <TRIPHeader> element, followed by all the subsequent <DebtRecord> elemetns under it (and <TRIPHeader>and <DebtRecord> at the same hierarchy) so my query results look like:


Code: 
<TRIPHeader>
      (information)
</TRIPHeader>
<DebtRecord>
      (information)
</DebtRecord>
<DebtRecord>
      (information)
</DebtRecord>
<DebtRecord>
      (information)
</DebtRecord>
...
...


I hope even some of that makes sense....
 
Last edited:
Homerboy

Homerboy

Lifer
Mar 1, 2000
30,890
5,001
126
I think I've got it.

Holy Damn. It's is ALWAYS the EASIEST solution too...

Code: 
SELECT 
(
...QUERY 1...
),
(
...QUERY 2....
)
FOR XML PATH('')

That seems to give me one XML formatted result, in what appears to be the format I want.

Holy hell if that is it... and it was THAT easy, I hope my boss doesn't realize the amount of time I have wasted :)
 
You must log in or register to reply here.

TRENDING THREADS

COMPANY
RESOURCES
FOLLOW
Top Bottom