hard time with XML explicit
-
To all,
I am trying to write a complicated select query to retrieve data from a table in a specific XML format. Below is the format, and my query. Any help would be appreciated?
<units>
<corps itemName="V CORPS">
<msc itemName="1ST AD">
<submsc itemName="1-4 ADA (WGMBAA)">
</submsc>
<submsc itemName="2ND BRIGADE COMBAT TEAM">
</submsc>
<submsc itemName="4TH AVIATION BRIGADE">
<subsubmsc itemName="1-1 CAV (WAETAA)">
</submsc>
<submsc itemName = "TASK FORCE FALCON - 1ST AD">
<subsubmsc itemName = "1-35 AR (W4TA70)"/>
<subsubmsc itemName = "1-4 ADA (W4TA57)"/>
</submsc>
</msc>
<msc itemName = "1ST ID">
<submsc itemName = "2ND BDE">
<subsubmsc itemName = "1-18 IN (WAM4AA)"/>
<subsubmsc itemName = "1-26 IN (WAM3AA)"/>
</msc>
</corps>
</units>
ALTER PROCEDURE get_dosf_trms_units_xml
AS
select 1 AS Tag, null AS parent, NULL AS [units!1!units], NULL AS [corps!2!itemName1],
NULL AS [msc!3!itemName2], NULL AS [submsc!4!itemName3],
NULL AS [subsubmsc!5!itemName4]
from trms_units
where sims = 1
UNION
select distinct 2 AS Tag, 1 AS parent, NULL, corps, NULL, NULL, NULL
from trms_units
where column1 = 1
UNION
select distinct 3 AS Tag, 2 AS parent, NULL, NULL, msc, NULL, NULL
from trms_units
where column1 = 1
UNION
select distinct 4 AS Tag, 3 AS parent, NULL, NULL, NULL, sub_msc + ' (' + uic + ')', NULL
from trms_units
where column1 = 1
UNION
select distinct 5 AS Tag, 4 AS parent, NULL, NULL, NULL, NULL, sub_sub_msc + ' (' + uic + ')'
from trms_units
where column1 = 1
for xml explicit
-------------------------
Patrick Quinn
TRMS Database Administrator
Anteon Corporation
09641-83-7722, DSN 475-7722
-
I'ld need to see your schema, but assuming you have three tables:
tblCorps;tblMSC;tblSubMSC
Where tblMSC is joined to the PK of tblCorps
and tblSubMSC is joined to the PK of tblMSC.
The query below should work (extending it to the subsubmsc is left as an exercise for the reader 🙂 )
CREATE PROCEDURE Attention AS
SELECT DISTINCT1 as Tag,
NULL as Parent,
Corpsas [corps!1!ItemName],
NULL as [msc!2!ItemName],
NULLas [submsc!3!ItemName]
FROM tblCorps
UNION ALL
SELECT DISTINCT 2,
1,
tblCorps.Corps,
tblMSC.MSC,
Null
FROM
tblCorps,
tblMSC
WHERE tblMSC.CorpsID=tblCorps.CorpsID
UNION ALL
SELECT DISTINCT 3,
2,
tblCorps.Corps,
tblMSC.MSC,
tblSubMSC.SubMSC
FROM
tblCorps,
tblMSC,
tblSubMSC
WHERE tblMSC.CorpsID=tblCorps.CorpsID
AND tblSubMSC.MSCID=tblMSC.MSCID
ORDER BY [corps!1!ItemName],[msc!2!ItemName]
FOR XML EXPLICIT
GO
-
Well, in fact, the corps, msc, submsc, subsubmsc fields are only from one table with one primary key - PK1. How does this affect the XML query?
Patrick Quinn
TRMS Database Administrator
-------------------------
Patrick Quinn
TRMS Database Administrator
Anteon Corporation
09641-83-7722, DSN 475-7722
-
How is the data structure shown in your XML example represented in the trms_units table? A bit more info about the data might help.
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it. -
I am pulling all data from that table.
Patrick Quinn
TRMS Database Administrator
-------------------------
Patrick Quinn
TRMS Database Administrator
Anteon Corporation
09641-83-7722, DSN 475-7722
-
The XML you provide shows data in a relational structure. You say all of the data is stored in a single table. How does the relational data map to the single flat table? What do the columns column1 and sims represent?
What do you get back when you run your get_dosf_trms_units_xml procedure? Is there an error or just incorrect XML format?
It looks as though what you're missing may just be an order by clause but it's difficult to tell with the limited information given.
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it. -
I am pulling all data from that table.
Patrick Quinn
TRMS Database Administrator
-------------------------
Patrick Quinn
TRMS Database Administrator
Anteon Corporation
09641-83-7722, DSN 475-7722
-
sims and column1 are one in the same. These columns along all of the other columns are coming from just table. The format I gave represents the hierarchy of the different columns. When I try to run this query with an order by clause, I get this error message: "Parent Tag ID # is not among the open tags. FOR XML EXPLICIT requirement requires parent tags to be opened first." I did not know how to fix this run-time message, so I took out the order by clause. Again, I am basically looking for how this query should look if all data is coming from one table.
-------------------------
Patrick Quinn
TRMS Database Administrator
Anteon Corporation
09641-83-7722, DSN 475-7722
-
Thanks for the additional info. First off, you will need to select any columns in the order by clause in each UNION select statement. It's important that you select the exact same columns for what you want to order by otherwise you will still see the error about missing open tags.
I think what you need is something like the following (plus signs are not showing properly when I preview - hopefully they will when I post!):
ALTER PROCEDURE get_dosf_trms_units_xml
AS
select 1 AS Tag, null AS parent, NULL AS [units!1!units], NULL AS [corps!2!itemName1],
NULL AS [msc!3!itemName2], NULL AS [submsc!4!itemName3],
NULL AS [subsubmsc!5!itemName4]
from trms_units
where sims = 1
UNION
select distinct 2 AS Tag, 1 AS parent, NULL, corps, NULL, NULL, NULL
from trms_units
where column1 = 1
UNION
select distinct 3 AS Tag, 2 AS parent, NULL, corps, msc, NULL, NULL
from trms_units
where column1 = 1
UNION
select distinct 4 AS Tag, 3 AS parent, NULL, corps, msc, sub_msc + ' (' + uic + ')', NULL
from trms_units
where column1 = 1
UNION
select distinct 5 AS Tag, 4 AS parent, NULL, corps, msc, sub_msc + ' (' + uic + ')', sub_sub_msc + ' (' + uic + ')'
from trms_units
where column1 = 1
order by [corps!2!itemName], [msc!3!itemName],[submsc!4!itemName], [subsubmsc!5!itemName]
for xml explicit
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it. -
Greetings Mia,
Thanks for your help. I think it is producing the XML format I am seeking. I did not realize how important the order by was.
Sincerely,
Patrick Quinn
-------------------------
Patrick Quinn
TRMS Database Administrator
Anteon Corporation
09641-83-7722, DSN 475-7722
-
Glad to be of help Patrick - I only knew where to look for the problem because I've had exactly the same error myself when I started using FOR XML EXPLICIT
Cheers,
mia
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply