asked on

FOR XML EXPLICIT--xml structure question

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.

Hilaire

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

ASKER CERTIFIED SOLUTION

Hilaire

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Hilaire

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

thedude112286

ASKER

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.

Hilaire

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

Hilaire

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

thedude112286

ASKER

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.

Hilaire

>> 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