help formating xml data differently

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

help formating xml data differently

Author

Topic

samssb
Starting Member

10 Posts

Posted - 2006-11-16 : 10:22:27

I am currently lost on how to format data in the manner i would like. I am getting xml output that looks like this, but i would like to only have HomeFileID show one time and have both addresses underneath it.

<row>
<HomeFileID>4</HomeFileID>
<Type>Home</Type>
<Address1>2727 Gilbert St</Address1>
<Address2></Address2>
<Address3></Address3>
<City>Iowa City</City>
<State>IA</State>
<Zip>54269</Zip>
<Directions>Down the street.</Directions>
</row>
<row>
<HomeFileID>4</HomeFileID>
<Type>Vacation</Type>
<Address1>5 Elmwood Ave</Address1>
<Address2></Address2>
<Address3></Address3>
<City>Cedar Rapids</City>
<State>IA</State>
<Zip>87652</Zip>
<Zip4></Zip4>
<Directions>Down the street.</Directions>
</row>
<row>
<HomeFileID>11</HomeFileID>
<Type>Home</Type>
<Address1>713 Grand Ave</Address1>
<Address2></Address2>
<Address3></Address3>
<City>Moline</City>
<State>IL</State>
<Zip>61282</Zip>
</row>

I would really like the data to look like this and i may possibly throw an identifier in there for each address:

<row>
<HomeFileID>4</HomeFileID>
<Type>Home</Type>
<Address1>2727 Gilbert St</Address1>
<Address2></Address2>
<Address3></Address3>
<City>Iowa City</City>
<State>IA</State>
<Zip>54269</Zip>
<Directions>Down the street.</Directions>
<Type>Vacation</Type>
<Address1>5 Elmwood Ave</Address1>
<Address2></Address2>
<Address3></Address3>
<City>Cedar Rapids</City>
<State>IA</State>
<Zip>87652</Zip>
<Zip4></Zip4>
<Directions>Down the street.</Directions>
</row>
<row>
<HomeFileID>11</HomeFileID>
<Type>Home</Type>
<Address1>713 Grand Ave</Address1>
<Address2></Address2>
<Address3></Address3>
<City>Moline</City>
<State>IL</State>
<Zip>61282</Zip>
</row>

Here is my current query:

SELECT DISTINCT A.HomeFileID, A.Type, A.Info,
A.Address1, A.Address2, A.Address3, A.City, A.State,
A.Zip, A.Zip4, Convert(varchar(50), D.Directions) as Directions
FROM Addresses A LEFT JOIN Directions D ON (A.AddressID = D.AddressID)
LEFT JOIN HomeFiles HF ON (A.HomeFileID = HF.HomeFileID)
Where HF.Tag = 1
GROUP BY A.HomeFileID, A.Type, A.Info, A.Address1, A.Address2, A.Address3,
A.City, A.State, A.Zip, A.Zip4, Convert(varchar(50), D.Directions)
ORDER BY A.HomeFileID, A.Type
FOR XML PATH(''), ROOT('root')

Any help would be much appreciated.

Kristen
Test

22859 Posts

Posted - 2006-11-16 : 13:38:59

To get Parent / Child XML you have to use UNIONs with TAG and PARENT columns tying them together, and your sample Output needs a bit more structure - e.g.


<row>
    <HomeFileID>4</HomeFileID>
    <Address>
        <Type>Home</Type>
        <Address1>2727 Gilbert St</Address1>
        <Address2></Address2>
        <Address3></Address3>
        <City>Iowa City</City>
        <State>IA</State>
        <Zip>54269</Zip>
        <Directions>Down the street.</Directions>
    </Address>
    <Address>
        <Type>Vacation</Type>
        <Address1>5 Elmwood Ave</Address1>
        <Address2></Address2>
        <Address3></Address3>
        <City>Cedar Rapids</City>
        <State>IA</State>
        <Zip>87652</Zip>
        <Zip4></Zip4>
        <Directions>Down the street.</Directions>
    </Address>
</row>

SELECT
	[Tag] = 10,
	[Parent] = NULL,
	[row!10!HomeFileID!element]	= HomeFileID,
	[Address!20!Type!element]		= NULL,
	[Address!20!Address1!element]		= NULL,
	...
FROM HomeFiles
UNION ALL
SELECT
	[Tag] = 20,
	[Parent] = 10,
	[row!10!HomeFileID!element]	= NULL,
	[Address!20!Type!element]	= Type,
	[Address!20!Address1!element]	= Address1,
	...
FROM Addresses
...
ORDER BY [row!10!HomeFileID!element],  [Address!20!Type!element], [Tag]
FOR XML EXPLICIT

See BoL for more details

Krsiten

samssb
Starting Member

10 Posts

Posted - 2006-11-16 : 15:10:12

Thanks a lot. That puts it right into the format the user wants. Guess i should study up on 'for xml explicit'.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-16 : 15:16:10

Using FOR XML PATH in SQL Server 2005, you can get the nesting you want with subqueries, and control things very nicely using column names. Here's an example


SELECT HF.HomeFileID as [@HomeFileID],
(SELECT A.[Type], A.Info, 
A.Address1, A.Address2, A.Address3, A.City, A.State, 
A.Zip, A.Zip4, Convert(varchar(50), D.Directions) as [Directions]
FROM Addresses A
LEFT JOIN Directions D ON A.AddressID = D.AddressID
WHERE A.HomeFileID = HF.HomeFileID
ORDER BY A.[Type]
FOR XML PATH('Home'), type)
FROM HomeFiles HF
WHERE HF.Tag = 1
ORDER BY HF.HomeFileID
FOR XML PATH('Homes'), ROOT('root')

This will produce XML something like this


<root>
<Homes HomeFileID="4">
   <Home>
      <Type>Home</Type>
      <Address1>2727 Gilbert</Address1>
      ...
      <Directions>down the street</Directions>
   </Home>
   <Home>
      <Type>Vacation</Type>
      <Address1>5 Elmwood</Address1>
      ...
      <Directions>down the street</Directions>
   </Home>
</Homes>
<Homes HomeFileID="11">
   <Home>
      <Type>Home</Type>
      <Address1>713 Grand</Address1>
      ...
      <Directions>down the street</Directions>
   </Home>
</Homes>
</root>

Kristen
Test

22859 Posts

Posted - 2006-11-16 : 15:39:26

Its still ghastly "write-only"syntax, isn't it snSQL? Or is it just me??

Perhaps I'd better put down that deposit on a plot in the churchyard already ...

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-16 : 16:17:11

I don't know, beats FOR XML EXPLICIT I think. Not exactly sure what you mean by "write-only" syntax?

It certainly beats writing a regular SELECT and then generating the XML myself on the client

I guess I look at it as - is it any worse than SQL felt the first time I worked with it?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-16 : 18:03:31

One more thing, you could have used FOR XML AUTO or FOR XML AUTO, ELEMENTS for this query too. It will do the nesting for you automatically but you won't have anywhere near the control you do with FOR XML PATH.

samssb
Starting Member

10 Posts

Posted - 2006-11-17 : 10:42:56

I am working with both ways. But i am having a problem with a new FOR XML PATH query. I seem to be getting a cartesion join or something. This query is returning all certificate info for every individual when i only want to return their certificate info. not sure what i am missing. Here is my query:

DECLARE @Today datetime
SET @Today = GETDATE()

SELECT MP.HFID as HomeFileID, MP.First_Name, MP.Middle, MP.Last_Name,
MP.SocSec, MP.DOB, MP.Sex, MP.Tob, XPT.Data AS Type, MP.MyPeople_Order,
DATEDIFF(YY,DOB,@Today)- CASE WHEN DOB <=
DATEADD(YY,DATEDIFF(yy,@Today,DOB), @Today)
THEN 0 ELSE 1 END AS "Age",
(SELECT DISTINCT IML.Cert_Member_No, IML.Cert_Member_No_Suf,
IML.CV_Plan_Desc
FROM InsuranceMyLife IML
WHERE HF.Tag = 1 and HF.HomeFileID = MP.HFID
ORDER BY IML.Cert_Member_No, IML.Cert_Member_No_Suf,
IML.CV_Plan_Desc
FOR XML PATH('Certificate'), type)
FROM InsuranceMyLife IML LEFT JOIN MyPeople MP ON
(IML.Cert_Member_No = MP.MasterCertNo) AND
(IML.Cert_Member_No_Suf = MP.MasterCertSufx) LEFT JOIN HomeFiles HF ON
(MP.HFID = HF.HomeFileID) LEFT JOIN luptrPeopleTypes XPT ON
(MP.Type = XPT.Type)
WHERE HF.Tag = 1
ORDER BY HF.HomeFileID
FOR XML PATH('HomeFile'), root('root')

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-17 : 12:03:15

In your certificate subquery you aren't filtering based on the outer query, and given that you are not using the InsuranceMyLife table in the outer query, you shouldn't include it in the outer query, I'm not sure what your data looks like so this is a guess, but something like this should work


DECLARE @Today datetime 
SET @Today = GETDATE()

SELECT MP.HFID as HomeFileID, MP.First_Name, MP.Middle, MP.Last_Name,
MP.SocSec, MP.DOB, MP.Sex, MP.Tob, XPT.Data AS Type, MP.MyPeople_Order,
DATEDIFF(YY,DOB,@Today)- CASE WHEN DOB <= 
DATEADD(YY,DATEDIFF(yy,@Today,DOB), @Today)
THEN 0 ELSE 1 END AS "Age", 
(SELECT DISTINCT IML.Cert_Member_No, IML.Cert_Member_No_Suf,
IML.CV_Plan_Desc
FROM InsuranceMyLife IML
WHERE (IML.Cert_Member_No = MP.MasterCertNo) AND
(IML.Cert_Member_No_Suf = MP.MasterCertSufx)
ORDER BY IML.Cert_Member_No, IML.Cert_Member_No_Suf, 
IML.CV_Plan_Desc
FOR XML PATH('Certificate'), type)
FROM MyPeople MP ON
LEFT JOIN HomeFiles HF ON (MP.HFID = HF.HomeFileID) 
LEFT JOIN luptrPeopleTypes XPT ON (MP.Type = XPT.Type) 
WHERE HF.Tag = 1 
ORDER BY HF.HomeFileID
FOR XML PATH('HomeFile'), root('root')

samssb
Starting Member

10 Posts

Posted - 2006-11-17 : 12:47:01

Thanks, works great. I am also having trouble with 1 other query. It lists certificate information for each hfid. When there is a hfid with 2 certificates it lists both within the HFID tag, but then it lists the entire tag again a second time. If there are 3 it lists the entire hfid tag 3 times. Probably just another simple fix i cannot seem to figure out. Here is the query:

SELECT MP.HFID, MP.DOB, MP.SocSec, I.Name,
(SELECT DISTINCT I.Certificate, I.CertificateSufx,
I.cv_mode, I.cert_premium,
I.cv_cl_annual_premium, I.cv_plan_desc, I.cv_ins_amt,
I.cv_eff_dt, I.cv_cash_valuemsg, I.cv_trssclft_lnbal,
I.cv_surr_valuemsg
FROM InsuranceMyLife I
WHERE (MP.MasterCertSufx = I.Cert_Member_No_Suf)
AND (MP.MasterCertNo = I.Cert_Member_No)
ORDER BY I.Certificate, I.CertificateSufx
FOR XML PATH('Certificate'), type)
FROM MyPeople MP LEFT JOIN InsuranceMyLife I ON
((MP.MasterCertSufx = I.Cert_Member_No_Suf)
AND (MP.MasterCertNo = I.Cert_Member_No))
LEFT JOIN HomeFiles HF ON (MP.HFID = HF.HomeFileID)
WHERE HF.Tag = 1
ORDER BY HF.HomeFileID
FOR XML PATH('HomeFileID'), root('root')

samssb
Starting Member

10 Posts

Posted - 2006-11-17 : 15:08:46

This query seems to work fine when i take out the reference to insurancemylife in the outer query. Here is how it looks:

SELECT MP.HFID, convert(varchar(10),MP.DOB, 101) DOB, MP.SocSec, --I.Name,
(SELECT DISTINCT I.Name, I.Certificate, I.CertificateSufx,
I.cv_mode, I.cert_premium,
I.cv_cl_annual_premium, I.cv_plan_desc, I.cv_ins_amt,
I.cv_eff_dt, I.cv_cash_valuemsg, I.cv_trssclft_lnbal,
I.cv_surr_valuemsg
FROM InsuranceMyLife I
WHERE (MP.MasterCertSufx = I.Cert_Member_No_Suf)
AND (MP.MasterCertNo = I.Cert_Member_No)
ORDER BY I.Certificate, I.CertificateSufx
FOR XML PATH('Certificate'), type)
FROM MyPeople MP --LEFT JOIN InsuranceMyLife I ON
--((MP.MasterCertSufx = I.Cert_Member_No_Suf)
--AND (MP.MasterCertNo = I.Cert_Member_No))
LEFT JOIN HomeFiles HF ON (MP.HFID = HF.HomeFileID)
WHERE HF.Tag = 1
ORDER BY HF.HomeFileID
FOR XML PATH('HomeFileID'), root('root')

I was just wondering if it was possible to do it the other way. I would like to have the name in the first select statement.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-17 : 16:52:45

quote:
I was just wondering if it was possible to do it the other way. I would like to have the name in the first select statement.

It's possible, but as you've seen then you get duplications, the point is that either the name is the same on every certificate, in which case it shouldn't be in the certificate table, it should be in the MyPeople table OR it can be different for every certificate in which case it doesn't make sense to put it in the first select because it returns multiple values.

There are ways to grab just one of the names in that case, but which one would be the right one?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources