?
Solved

FOR XML EXPLICIT--xml structure question

Posted on 2005-04-13
8
Medium Priority
?
602 Views
Last Modified: 2013-11-19
I have the following sproc:

CREATE PROC GetByName
(
      @Name nvarchar(1024)
)
AS
BEGIN
      SET NOCOUNT ON

      SELECT 1 AS Tag, NULL AS Parent,
            Table1.Name AS [Doc!1!Name],
            Table1.Text AS [Text!1!cdata],
            Table1.Tag AS [Tag!1!cdata],
            Table2.Dimension AS [Dim!1!Dim],
            Table2.Value AS [Val!1!Val]
      FROM Table1
      LEFT JOIN Table2
            ON Table2.SourceID=Table1.ID
      WHERE Table1.Name=@Name
      FOR XML EXPLICIT, BINARY BASE64
END
GO

Now, Table2 contains multiple dimension-value pairs for each id in Table1.  I want to get a result that looks like this:

<Document Name="Table1.Name">
     <Text>Table1.Text</Text>
     <Tag>Table1.Tag</Tag>
     <Table2>
          <Dim Name="Table2.Dimension" />
               <Value>Table2.Value</Value>
          <Dim Name="Table2.Dimension" />
               <Value>Table2.Value</Value>
           ...
     </Table2>
</Document>

How can I structure my query to get results in that format?

Thank you very much.
0
Comment
Question by:thedude112286
  • 6
  • 2
8 Comments
 
LVL 26

Expert Comment

by:Hilaire
ID: 13779995
This should give you a (good ?) start ...

select 1 as tag, null as parent, Name as [Document!1!Name], text as [Document!1!Text!element],
tag as [Document!1!Tag!element],null as [Table2!2], null as [Dim!3!name], null as [Value!4]
from table1
union all
select 2 as tag, 1 as parent, Name as [Document!1!Name], text as [Document!1!Text!element],
tag as [Document!1!Tag!element],'' as [Table2!2], null as [Dim!3!name], null as [Value!4]
from table1
union all
select 3 as tag, 2 as parent, Name as [Document!1!Name], text as [Document!1!Text!element],
tag as [Document!1!Tag!element],'' as [Table2!2], dimension as [Dim!3!name], null as [Value!4]
from table1 T1 inner join table2 T2 ON T2.SourceID=T1.ID
union all
select 4 as tag, 2 as parent, Name as [Document!1!Name], text as [Document!1!Text!element],
tag as [Document!1!Tag!element],'' as [Table2!2], dimension as [Dim!3!name], value as [Value!4]
from table1 T1 inner join table2 T2 ON T2.SourceID=T1.ID
FOR XML EXPLICIT, BINARY BASE64
   
0
 
LVL 26

Accepted Solution

by:
Hilaire earned 2000 total points
ID: 13780669

Here's my second stab,
less verbose and - i guess - closer to what you need (fixed dim name/value order)


select 1 as tag, null as parent, Name as [Document!1!Name], text as [Document!1!Text!element],
      tag as [Document!1!Tag!element],null as [Table2!2], null as [Dim!3!sortkey!hide],
      null as [Dim!3!Name], null as [Value!4]
from Table1
WHERE Table1.Name=@Name
union all
select 2, 1, Name, text, tag,'', null, null, null
from Table1
WHERE Table1.Name=@Name
union all
select 3, 2, T1.Name, T1.text, T1.tag, '', T2.id, T2.dimension, null
from Table1 T1 inner join Table2 T2 ON T2.SourceID=T1.ID
WHERE T1.Name=@Name
union all
select 4, 2, T1.Name, T1.text, T1.tag, '', T2.id, T2.dimension, T2.value
from Table1 T1 inner join Table2 T2 ON T2.SourceID=T1.ID
WHERE T1.Name=@Name
order by [Document!1!Name], [Dim!3!sortkey!hide], [Value!4]
FOR XML EXPLICIT, BINARY BASE64
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 13788990
Hi thedude112286,
did you try my code above ?
your feedback is welcome if you think it still needs a little tweaking.
Just let me know ...

Hilaire
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 4

Author Comment

by:thedude112286
ID: 13793797
I can't thank you enough.  Your code works beautifully.  Right now, it generates output in this form:

<Document Name="Name">
      <Text>Text</Text>
      <Tag></Tag>
      <Table2>
            <Dim Name="key"/>
            <Value>1.000000000000000e+000</Value>
            <Dim Name="key1"/>
            <Value>2.000000000000000e+000</Value>
      </Table2>
</Document>

This is terrific.  If possible, I'd have liked the Value elements to be children of the Dim elements (just because I don't think XML is order specific and I wanted to play it safe), but I can't figure out how to do this and it's not a real big deal if they are siblings.

Again, thank you very much for all of your help.
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 13804191
New version modified as per your request,

select 1 as tag, null as parent, Name as [Document!1!Name], text as [Document!1!Text!element],
     tag as [Document!1!Tag!element],null as [Table2!2], null as [Dim!3!sortkey!hide],
     null as [Dim!3!Name], null as [Dim!3!Value!element]
from Table1
WHERE Table1.Name=@Name
union all
select 2, 1, Name, text, tag,'', null, null, null
from Table1
WHERE Table1.Name=@Name
union all
select 3, 2, T1.Name, T1.text, T1.tag, '', T2.id, T2.dimension, T2.value
from Table1 T1 inner join Table2 T2 ON T2.SourceID=T1.ID
WHERE T1.Name=@Name
order by [Document!1!Name], [Dim!3!sortkey!hide], [Value!4]
FOR XML EXPLICIT, BINARY BASE64
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 13805051
Oops typo in the order by

select 1 as tag, null as parent, Name as [Document!1!Name], text as [Document!1!Text!element],
     tag as [Document!1!Tag!element],null as [Table2!2], null as [Dim!3!sortkey!hide],
     null as [Dim!3!Name], null as [Dim!3!Value!element]
from Table1
WHERE Table1.Name=@Name
union all
select 2, 1, Name, text, tag,'', null, null, null
from Table1
WHERE Table1.Name=@Name
union all
select 3, 2, T1.Name, T1.text, T1.tag, '', T2.id, T2.dimension, T2.value
from Table1 T1 inner join Table2 T2 ON T2.SourceID=T1.ID
WHERE T1.Name=@Name
order by [Document!1!Name], [Dim!3!sortkey!hide]
FOR XML EXPLICIT, BINARY BASE64
0
 
LVL 4

Author Comment

by:thedude112286
ID: 13811340
I can't thank you enough.  You really helped me out.  I don't know if it's legal or how I would go about getting you more points, but you certainly deserve them.  This whole XML EXPLICIT syntax baffles me.

Again, thank you very much.
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 13813261
>> I don't know if it's legal or how I would go about getting you more points<<
A question can't be affected more than 500 points, but don't worry, most experts don't care that much about points. Personnaly I'm here to help, so your satisfaction is part of the reward ;-). Besides that, EE helps us to keep a good level and practice in cutting-edge techs, learn new tricks, ...

Thanks for your feedback.
Hilaire
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.
Suggested Courses

850 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