For/Next loop to process INSERT INTO statement

I reposted this because I was unable to achieve desired results from
previous recommendations.
Previous Post:
=================== I have the following code which does the following:
1. Deletes all rows having a value of "0" in column C
2. Uploads the data in Row 2 to my SQL Server
What I need for the code to do is to upload all rows on the worksheet. My
thought is that I might need a For/Next Loop, but I'm not sure where in the
code to place it. Any ideas on how/where would I modify the code to enable
it to (loop through) upload all rows, or iterate on each row having
data (those not deleted by the DeleteBlankRows procedure)?
Here's my code:
======================== Private Sub DeleteBlankRows()
Dim lastrow As Long
Dim r As Long
lastrow = Range("C" & Rows.Count).End(xlUp).Row
For r = lastrow To 2 Step -1
If Application.CountIf(Cells(r, "C").Resize(1, 1), 0) = 1 Then
ActiveSheet.Rows(r).Delete
End If
Next
End Sub
Sub InsertData()
Dim oConn As Object
Dim sSQL As String
Application.ScreenUpdating = False
Set wsSheet = ActiveWorkbook.Sheets("Product Tracking")
Set oConn = CreateObject("ADODB.Connection")
oConn.Open = "Provider=sqloledb;" & _
"Data Source=xx.x.xx.xx;" & _
"Initial Catalog=xxx_xxx;" & _
"User Id=xxxx;" & _
"Password=xxxx"
sSQL = "INSERT INTO Upload_Specific " & _
"([Location], [Product Type], [Quantity], [Product Name], [Style],
[Features]) " & _
" VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '" &
Range("C2").Value & "', '" & Range("D2").Value & "', '" & Range("E2").Value
& "', '" & _
Range("F2").Value & "')"
oConn.Execute sSQL
oConn.Close
Set oConn = Nothing
End Sub
Thanks in advance.Try changing
sSQL = "INSERT INTO Upload_Specific " & _
"([Location], [Product Type], [Quantity], [Product Name], [Style],
[Features]) " & _
" VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '" &
Range("C2").Value & "', '" & Range("D2").Value & "', '" & Range("E2").Value
& "', '" & _
Range("F2").Value & "')"
oConn.Execute sSQL
to
For i = 2 To Range("A65536").End(xlUp).Row
sSQL = "INSERT INTO Upload_Specific " & _
"([Location], [Product Type], [Quantity], [Product Name], [Style],
[Features]) " & _
" VALUES ('" & Range("A"&i).Value & "', '" & Range("B"&i).Value & "', '" & _
Range("C"&i).Value & "', '" & Range("D"&i).Value & "', '" & _
Range("E"&i).Value & "', '" & _
Range("F"&i).Value & "')"
oConn.Execute sSQL
Next i
HTH,
Bernie
MS Excel MVP
"Doctorjones_md" <xxxDoctorjones_mdxxx@.xxxyahoo.com> wrote in message
news:uQqUzX5IHHA.1816@.TK2MSFTNGP06.phx.gbl...
>I reposted this because I was unable to achieve desired results from previous recommendations.
> Previous Post:
> ===================> I have the following code which does the following:
> 1. Deletes all rows having a value of "0" in column C
> 2. Uploads the data in Row 2 to my SQL Server
> What I need for the code to do is to upload all rows on the worksheet. My thought is that I might
> need a For/Next Loop, but I'm not sure where in the code to place it. Any ideas on how/where
> would I modify the code to enable it to (loop through) upload all rows, or iterate on each row
> having
> data (those not deleted by the DeleteBlankRows procedure)?
>
> Here's my code:
> ========================> Private Sub DeleteBlankRows()
> Dim lastrow As Long
> Dim r As Long
> lastrow = Range("C" & Rows.Count).End(xlUp).Row
> For r = lastrow To 2 Step -1
> If Application.CountIf(Cells(r, "C").Resize(1, 1), 0) = 1 Then
> ActiveSheet.Rows(r).Delete
> End If
> Next
> End Sub
> Sub InsertData()
> Dim oConn As Object
> Dim sSQL As String
> Application.ScreenUpdating = False
> Set wsSheet = ActiveWorkbook.Sheets("Product Tracking")
> Set oConn = CreateObject("ADODB.Connection")
> oConn.Open = "Provider=sqloledb;" & _
> "Data Source=xx.x.xx.xx;" & _
> "Initial Catalog=xxx_xxx;" & _
> "User Id=xxxx;" & _
> "Password=xxxx"
> sSQL = "INSERT INTO Upload_Specific " & _
> "([Location], [Product Type], [Quantity], [Product Name], [Style],
> [Features]) " & _
> " VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '" &
> Range("C2").Value & "', '" & Range("D2").Value & "', '" & Range("E2").Value
> & "', '" & _
> Range("F2").Value & "')"
> oConn.Execute sSQL
> oConn.Close
> Set oConn = Nothing
> End Sub
> Thanks in advance.
>|||Bernie -- Thanks a Bunch -- that did the trick Brillantly!!
I was getting "Wrapped Around the Axle" trying to deal with Stored
Procedures and Bulk Inserts (and whatnot) -- what I originally had worked
(somewhat), but just needed some tweaking -- thanks again for you help and
quick response. :)
"Bernie Deitrick" <deitbe @. consumer dot org> wrote in message
news:OJ9VN15IHHA.4848@.TK2MSFTNGP04.phx.gbl...
> Try changing
> sSQL = "INSERT INTO Upload_Specific " & _
> "([Location], [Product Type], [Quantity], [Product Name], [Style],
> [Features]) " & _
> " VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '"
> &
> Range("C2").Value & "', '" & Range("D2").Value & "', '" &
> Range("E2").Value
> & "', '" & _
> Range("F2").Value & "')"
> oConn.Execute sSQL
> to
> For i = 2 To Range("A65536").End(xlUp).Row
> sSQL = "INSERT INTO Upload_Specific " & _
> "([Location], [Product Type], [Quantity], [Product Name], [Style],
> [Features]) " & _
> " VALUES ('" & Range("A"&i).Value & "', '" & Range("B"&i).Value & "',
> '" & _
> Range("C"&i).Value & "', '" & Range("D"&i).Value & "', '" & _
> Range("E"&i).Value & "', '" & _
> Range("F"&i).Value & "')"
> oConn.Execute sSQL
> Next i
> HTH,
> Bernie
> MS Excel MVP
>
> "Doctorjones_md" <xxxDoctorjones_mdxxx@.xxxyahoo.com> wrote in message
> news:uQqUzX5IHHA.1816@.TK2MSFTNGP06.phx.gbl...
>>I reposted this because I was unable to achieve desired results from
>>previous recommendations.
>> Previous Post:
>> ===================>> I have the following code which does the following:
>> 1. Deletes all rows having a value of "0" in column C
>> 2. Uploads the data in Row 2 to my SQL Server
>> What I need for the code to do is to upload all rows on the worksheet.
>> My thought is that I might need a For/Next Loop, but I'm not sure where
>> in the code to place it. Any ideas on how/where would I modify the code
>> to enable it to (loop through) upload all rows, or iterate on each row
>> having
>> data (those not deleted by the DeleteBlankRows procedure)?
>>
>> Here's my code:
>> ========================>> Private Sub DeleteBlankRows()
>> Dim lastrow As Long
>> Dim r As Long
>> lastrow = Range("C" & Rows.Count).End(xlUp).Row
>> For r = lastrow To 2 Step -1
>> If Application.CountIf(Cells(r, "C").Resize(1, 1), 0) = 1 Then
>> ActiveSheet.Rows(r).Delete
>> End If
>> Next
>> End Sub
>> Sub InsertData()
>> Dim oConn As Object
>> Dim sSQL As String
>> Application.ScreenUpdating = False
>> Set wsSheet = ActiveWorkbook.Sheets("Product Tracking")
>> Set oConn = CreateObject("ADODB.Connection")
>> oConn.Open = "Provider=sqloledb;" & _
>> "Data Source=xx.x.xx.xx;" & _
>> "Initial Catalog=xxx_xxx;" & _
>> "User Id=xxxx;" & _
>> "Password=xxxx"
>> sSQL = "INSERT INTO Upload_Specific " & _
>> "([Location], [Product Type], [Quantity], [Product Name], [Style],
>> [Features]) " & _
>> " VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '"
>> &
>> Range("C2").Value & "', '" & Range("D2").Value & "', '" &
>> Range("E2").Value
>> & "', '" & _
>> Range("F2").Value & "')"
>> oConn.Execute sSQL
>> oConn.Close
>> Set oConn = Nothing
>> End Sub
>> Thanks in advance.
>>
>|||Doctor Jones,
I have to believe that there is a way to move an entire table into a database without looping. But
I have no experience with SQL, and have never used code like yours - still, I'm glad to hear that my
(probably sub-optimal) code worked out for you.
Bernie
MS Excel MVP
"Doctorjones_md" <xxxDoctorjones_mdxxx@.xxxyahoo.com> wrote in message
news:uhXwiI6IHHA.816@.TK2MSFTNGP06.phx.gbl...
> Bernie -- Thanks a Bunch -- that did the trick Brillantly!!
> I was getting "Wrapped Around the Axle" trying to deal with Stored Procedures and Bulk Inserts
> (and whatnot) -- what I originally had worked (somewhat), but just needed some tweaking -- thanks
> again for you help and quick response. :)
>
> "Bernie Deitrick" <deitbe @. consumer dot org> wrote in message
> news:OJ9VN15IHHA.4848@.TK2MSFTNGP04.phx.gbl...
>> Try changing
>> sSQL = "INSERT INTO Upload_Specific " & _
>> "([Location], [Product Type], [Quantity], [Product Name], [Style],
>> [Features]) " & _
>> " VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '" &
>> Range("C2").Value & "', '" & Range("D2").Value & "', '" & Range("E2").Value
>> & "', '" & _
>> Range("F2").Value & "')"
>> oConn.Execute sSQL
>> to
>> For i = 2 To Range("A65536").End(xlUp).Row
>> sSQL = "INSERT INTO Upload_Specific " & _
>> "([Location], [Product Type], [Quantity], [Product Name], [Style],
>> [Features]) " & _
>> " VALUES ('" & Range("A"&i).Value & "', '" & Range("B"&i).Value & "', '" & _
>> Range("C"&i).Value & "', '" & Range("D"&i).Value & "', '" & _
>> Range("E"&i).Value & "', '" & _
>> Range("F"&i).Value & "')"
>> oConn.Execute sSQL
>> Next i
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>> "Doctorjones_md" <xxxDoctorjones_mdxxx@.xxxyahoo.com> wrote in message
>> news:uQqUzX5IHHA.1816@.TK2MSFTNGP06.phx.gbl...
>>I reposted this because I was unable to achieve desired results from previous recommendations.
>> Previous Post:
>> ===================>> I have the following code which does the following:
>> 1. Deletes all rows having a value of "0" in column C
>> 2. Uploads the data in Row 2 to my SQL Server
>> What I need for the code to do is to upload all rows on the worksheet. My thought is that I
>> might need a For/Next Loop, but I'm not sure where in the code to place it. Any ideas on
>> how/where would I modify the code to enable it to (loop through) upload all rows, or iterate on
>> each row having
>> data (those not deleted by the DeleteBlankRows procedure)?
>>
>> Here's my code:
>> ========================>> Private Sub DeleteBlankRows()
>> Dim lastrow As Long
>> Dim r As Long
>> lastrow = Range("C" & Rows.Count).End(xlUp).Row
>> For r = lastrow To 2 Step -1
>> If Application.CountIf(Cells(r, "C").Resize(1, 1), 0) = 1 Then
>> ActiveSheet.Rows(r).Delete
>> End If
>> Next
>> End Sub
>> Sub InsertData()
>> Dim oConn As Object
>> Dim sSQL As String
>> Application.ScreenUpdating = False
>> Set wsSheet = ActiveWorkbook.Sheets("Product Tracking")
>> Set oConn = CreateObject("ADODB.Connection")
>> oConn.Open = "Provider=sqloledb;" & _
>> "Data Source=xx.x.xx.xx;" & _
>> "Initial Catalog=xxx_xxx;" & _
>> "User Id=xxxx;" & _
>> "Password=xxxx"
>> sSQL = "INSERT INTO Upload_Specific " & _
>> "([Location], [Product Type], [Quantity], [Product Name], [Style],
>> [Features]) " & _
>> " VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '" &
>> Range("C2").Value & "', '" & Range("D2").Value & "', '" & Range("E2").Value
>> & "', '" & _
>> Range("F2").Value & "')"
>> oConn.Execute sSQL
>> oConn.Close
>> Set oConn = Nothing
>> End Sub
>> Thanks in advance.
>>
>>
>|||Bernie,
There are a plethora of methods to Insert an entire Table, but your For/Next
code does exactly what I needed it to do.
Several other methods (OPENROWSET -- BULK INSERT) weren't working for me --
based on SQL Server Security Settings and/or other issues.
Thanks again for your help! :)
"Bernie Deitrick" <deitbe @. consumer dot org> wrote in message
news:eHaaO0DJHHA.4848@.TK2MSFTNGP04.phx.gbl...
> Doctor Jones,
> I have to believe that there is a way to move an entire table into a
> database without looping. But I have no experience with SQL, and have
> never used code like yours - still, I'm glad to hear that my (probably
> sub-optimal) code worked out for you.
> Bernie
> MS Excel MVP
>
> "Doctorjones_md" <xxxDoctorjones_mdxxx@.xxxyahoo.com> wrote in message
> news:uhXwiI6IHHA.816@.TK2MSFTNGP06.phx.gbl...
>> Bernie -- Thanks a Bunch -- that did the trick Brillantly!!
>> I was getting "Wrapped Around the Axle" trying to deal with Stored
>> Procedures and Bulk Inserts (and whatnot) -- what I originally had worked
>> (somewhat), but just needed some tweaking -- thanks again for you help
>> and quick response. :)
>>
>> "Bernie Deitrick" <deitbe @. consumer dot org> wrote in message
>> news:OJ9VN15IHHA.4848@.TK2MSFTNGP04.phx.gbl...
>> Try changing
>> sSQL = "INSERT INTO Upload_Specific " & _
>> "([Location], [Product Type], [Quantity], [Product Name], [Style],
>> [Features]) " & _
>> " VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "',
>> '" &
>> Range("C2").Value & "', '" & Range("D2").Value & "', '" &
>> Range("E2").Value
>> & "', '" & _
>> Range("F2").Value & "')"
>> oConn.Execute sSQL
>> to
>> For i = 2 To Range("A65536").End(xlUp).Row
>> sSQL = "INSERT INTO Upload_Specific " & _
>> "([Location], [Product Type], [Quantity], [Product Name], [Style],
>> [Features]) " & _
>> " VALUES ('" & Range("A"&i).Value & "', '" & Range("B"&i).Value & "',
>> '" & _
>> Range("C"&i).Value & "', '" & Range("D"&i).Value & "', '" & _
>> Range("E"&i).Value & "', '" & _
>> Range("F"&i).Value & "')"
>> oConn.Execute sSQL
>> Next i
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>> "Doctorjones_md" <xxxDoctorjones_mdxxx@.xxxyahoo.com> wrote in message
>> news:uQqUzX5IHHA.1816@.TK2MSFTNGP06.phx.gbl...
>>I reposted this because I was unable to achieve desired results from
>>previous recommendations.
>> Previous Post:
>> ===================>> I have the following code which does the following:
>> 1. Deletes all rows having a value of "0" in column C
>> 2. Uploads the data in Row 2 to my SQL Server
>> What I need for the code to do is to upload all rows on the worksheet.
>> My thought is that I might need a For/Next Loop, but I'm not sure where
>> in the code to place it. Any ideas on how/where would I modify the
>> code to enable it to (loop through) upload all rows, or iterate on each
>> row having
>> data (those not deleted by the DeleteBlankRows procedure)?
>>
>> Here's my code:
>> ========================>> Private Sub DeleteBlankRows()
>> Dim lastrow As Long
>> Dim r As Long
>> lastrow = Range("C" & Rows.Count).End(xlUp).Row
>> For r = lastrow To 2 Step -1
>> If Application.CountIf(Cells(r, "C").Resize(1, 1), 0) = 1 Then
>> ActiveSheet.Rows(r).Delete
>> End If
>> Next
>> End Sub
>> Sub InsertData()
>> Dim oConn As Object
>> Dim sSQL As String
>> Application.ScreenUpdating = False
>> Set wsSheet = ActiveWorkbook.Sheets("Product Tracking")
>> Set oConn = CreateObject("ADODB.Connection")
>> oConn.Open = "Provider=sqloledb;" & _
>> "Data Source=xx.x.xx.xx;" & _
>> "Initial Catalog=xxx_xxx;" & _
>> "User Id=xxxx;" & _
>> "Password=xxxx"
>> sSQL = "INSERT INTO Upload_Specific " & _
>> "([Location], [Product Type], [Quantity], [Product Name], [Style],
>> [Features]) " & _
>> " VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "',
>> '" &
>> Range("C2").Value & "', '" & Range("D2").Value & "', '" &
>> Range("E2").Value
>> & "', '" & _
>> Range("F2").Value & "')"
>> oConn.Execute sSQL
>> oConn.Close
>> Set oConn = Nothing
>> End Sub
>> Thanks in advance.
>>
>>
>>
>

FOR XML EXPLICIT formatting issue

I am trying to use FOR XML EXPLICIT to group records that are related.
I want to achieve something like
<Header Id="1" ... >
<Child1 Child1Id="1" Name="Fred" .../>
<Child1 Child1Id="2" Name="Tom" .../>
<Child2 Child2Id="3" Name="Dick" .../>
<Child2 Child2Id="4" Name="Harry" .../>
</Header>
<Header Id="2" ... >
<Child1 Child1Id="5" Name="Fred" .../>
<Child1 Child1Id="6" Name="Tom" .../>
<Child2 Child2Id="7" Name="Dick" .../>
<Child2 Child2Id="8" Name="Harry" .../>
</Header>
<Header Id="3" ... >
<Child1 Child1Id="9" Name="Fred" .../>
<Child1 Child1Id="10" Name="Tom" .../>
<Child2 Child2Id="11" Name="Dick" .../>
<Child2 Child2Id="12" Name="Harry" .../>
</Header>
I keep getting
<Header Id="1" ... >
<Header Id="2" ... />
<Header Id="3" ... />
<Child1 Child1Id="1" Name="Fred" .../>
<Child1 Child1Id="2" Name="Tom" .../>
<Child1 Child1Id="5" Name="Fred" .../>
<Child1 Child1Id="6" Name="Tom" .../>
<Child1 Child1Id="9" Name="Fred" .../>
<Child1 Child1Id="10" Name="Tom" .../>
<Child2 Child2Id="3" Name="Dick" .../>
<Child2 Child2Id="4" Name="Harry" .../>
<Child2 Child2Id="7" Name="Dick" .../>
<Child2 Child2Id="8" Name="Harry" .../>
<Child2 Child2Id="11" Name="Dick" .../>
<Child2 Child2Id="12" Name="Harry" .../>
The query looks something like
SELECT
1 AS Tag
, NULL AS Parent
, header AS 'Header!1!Id'
::
::
, NULL AS 'Child1!2!Child1Id'
, NULL AS 'Child1!2!Name'
, NULL AS 'Child2!3!Child1Id'
, NULL AS 'Child2!3!Name'
FROM
blah, blah, blah
UNION ALL
SELECT
1 AS Tag
, NULL AS Parent
, NULL AS 'Header!1!Id'
::
::
, ChildId AS 'Child1!2!Child1Id'
, Name AS 'Child1!2!Name'
, NULL AS 'Child2!3!Child1Id'
, NULL AS 'Child2!3!Name'
FROM
blah, blah, blah
UNION ALL
SELECT
1 AS Tag
, NULL AS Parent
, NULL AS 'Header!1!Id'
::
::
, NULL AS 'Child1!2!Child1Id'
, NULL AS 'Child1!2!Name'
, ChildId AS 'Child2!3!Child1Id'
, Name AS 'Child2!3!Name'
FROM
blah, blah, blah
Any help with either of these is gratefully appreciated.
Let me know if I am on the wrong path also, as I wouldn't be surprised
Thanks
Steve
You're missing an ORDER BY. Try this
create table #Header(HeaderId int)
insert into #Header(HeaderId) values(1)
insert into #Header(HeaderId) values(2)
insert into #Header(HeaderId) values(3)
create table #Child1(HeaderId int,Child1Id int,Name varchar(5))
create table #Child2(HeaderId int,Child2Id int,Name varchar(5))
insert into #Child1(HeaderId,Child1Id,Name) values(1,1,'Fred')
insert into #Child1(HeaderId,Child1Id,Name) values(1,2,'Tom')
insert into #Child2(HeaderId,Child2Id,Name) values(1,3,'Dick')
insert into #Child2(HeaderId,Child2Id,Name) values(1,4,'Harry')
insert into #Child1(HeaderId,Child1Id,Name) values(2,5,'Fred')
insert into #Child1(HeaderId,Child1Id,Name) values(2,6,'Tom')
insert into #Child2(HeaderId,Child2Id,Name) values(2,7,'Dick')
insert into #Child2(HeaderId,Child2Id,Name) values(2,8,'Harry')
insert into #Child1(HeaderId,Child1Id,Name) values(3,9,'Fred')
insert into #Child1(HeaderId,Child1Id,Name) values(3,10,'Tom')
insert into #Child2(HeaderId,Child2Id,Name) values(3,11,'Dick')
insert into #Child2(HeaderId,Child2Id,Name) values(3,12,'Harry')
SELECT 1 AS Tag
, NULL AS Parent
, h.HeaderId AS 'Header!1!Id'
, NULL AS 'Child1!2!Child1Id'
, NULL AS 'Child1!2!Name'
, NULL AS 'Child2!3!Child1Id'
, NULL AS 'Child2!3!Name'
FROM #Header h
UNION ALL
SELECT 2 AS Tag
, 1 AS Parent
, h.HeaderId AS 'Header!1!Id'
, c.Child1Id AS 'Child1!2!Child1Id'
, c.Name AS 'Child1!2!Name'
, NULL AS 'Child2!3!Child1Id'
, NULL AS 'Child2!3!Name'
FROM #Header h
INNER JOIN #Child1 c ON c.HeaderId=h.HeaderId
UNION ALL
SELECT 3 AS Tag
, 1 AS Parent
, h.HeaderId AS 'Header!1!Id'
, NULL AS 'Child1!2!Child1Id'
, NULL AS 'Child1!2!Name'
, c.Child2Id AS 'Child2!3!Child1Id'
, c.Name AS 'Child2!3!Name'
FROM #Header h
INNER JOIN #Child2 c ON c.HeaderId=h.HeaderId
ORDER BY h.HeaderId,Tag
FOR XML EXPLICIT
drop table #Child2
drop table #Child1
drop table #Header
|||"markc600@.hotmail.com" wrote:

> You're missing an ORDER BY. Try this
>
> create table #Header(HeaderId int)
> insert into #Header(HeaderId) values(1)
> insert into #Header(HeaderId) values(2)
> insert into #Header(HeaderId) values(3)
> create table #Child1(HeaderId int,Child1Id int,Name varchar(5))
> create table #Child2(HeaderId int,Child2Id int,Name varchar(5))
> insert into #Child1(HeaderId,Child1Id,Name) values(1,1,'Fred')
> insert into #Child1(HeaderId,Child1Id,Name) values(1,2,'Tom')
> insert into #Child2(HeaderId,Child2Id,Name) values(1,3,'Dick')
> insert into #Child2(HeaderId,Child2Id,Name) values(1,4,'Harry')
> insert into #Child1(HeaderId,Child1Id,Name) values(2,5,'Fred')
> insert into #Child1(HeaderId,Child1Id,Name) values(2,6,'Tom')
> insert into #Child2(HeaderId,Child2Id,Name) values(2,7,'Dick')
> insert into #Child2(HeaderId,Child2Id,Name) values(2,8,'Harry')
> insert into #Child1(HeaderId,Child1Id,Name) values(3,9,'Fred')
> insert into #Child1(HeaderId,Child1Id,Name) values(3,10,'Tom')
> insert into #Child2(HeaderId,Child2Id,Name) values(3,11,'Dick')
> insert into #Child2(HeaderId,Child2Id,Name) values(3,12,'Harry')
> SELECT 1 AS Tag
> , NULL AS Parent
> , h.HeaderId AS 'Header!1!Id'
> , NULL AS 'Child1!2!Child1Id'
> , NULL AS 'Child1!2!Name'
> , NULL AS 'Child2!3!Child1Id'
> , NULL AS 'Child2!3!Name'
> FROM #Header h
> UNION ALL
> SELECT 2 AS Tag
> , 1 AS Parent
> , h.HeaderId AS 'Header!1!Id'
> , c.Child1Id AS 'Child1!2!Child1Id'
> , c.Name AS 'Child1!2!Name'
> , NULL AS 'Child2!3!Child1Id'
> , NULL AS 'Child2!3!Name'
> FROM #Header h
> INNER JOIN #Child1 c ON c.HeaderId=h.HeaderId
> UNION ALL
> SELECT 3 AS Tag
> , 1 AS Parent
> , h.HeaderId AS 'Header!1!Id'
> , NULL AS 'Child1!2!Child1Id'
> , NULL AS 'Child1!2!Name'
> , c.Child2Id AS 'Child2!3!Child1Id'
> , c.Name AS 'Child2!3!Name'
> FROM #Header h
> INNER JOIN #Child2 c ON c.HeaderId=h.HeaderId
> ORDER BY h.HeaderId,Tag
> FOR XML EXPLICIT
>
> drop table #Child2
> drop table #Child1
> drop table #Header
>
Excellent, so close yet so far.
Also, in my actual code I had not propogated the Header Id into the other
parts of the union, it din't actually know what the relationships were.
And now BizTalk seems to like it.
Thanks

FOR XML EXPLICIT formatting issue

I am trying to use FOR XML EXPLICIT to group records that are related.
I want to achieve something like
<Header Id="1" ... >
<Child1 Child1Id="1" Name="Fred" .../>
<Child1 Child1Id="2" Name="Tom" .../>
<Child2 Child2Id="3" Name="Dick" .../>
<Child2 Child2Id="4" Name="Harry" .../>
</Header>
<Header Id="2" ... >
<Child1 Child1Id="5" Name="Fred" .../>
<Child1 Child1Id="6" Name="Tom" .../>
<Child2 Child2Id="7" Name="Dick" .../>
<Child2 Child2Id="8" Name="Harry" .../>
</Header>
<Header Id="3" ... >
<Child1 Child1Id="9" Name="Fred" .../>
<Child1 Child1Id="10" Name="Tom" .../>
<Child2 Child2Id="11" Name="Dick" .../>
<Child2 Child2Id="12" Name="Harry" .../>
</Header>
I keep getting
<Header Id="1" ... >
<Header Id="2" ... />
<Header Id="3" ... />
<Child1 Child1Id="1" Name="Fred" .../>
<Child1 Child1Id="2" Name="Tom" .../>
<Child1 Child1Id="5" Name="Fred" .../>
<Child1 Child1Id="6" Name="Tom" .../>
<Child1 Child1Id="9" Name="Fred" .../>
<Child1 Child1Id="10" Name="Tom" .../>
<Child2 Child2Id="3" Name="Dick" .../>
<Child2 Child2Id="4" Name="Harry" .../>
<Child2 Child2Id="7" Name="Dick" .../>
<Child2 Child2Id="8" Name="Harry" .../>
<Child2 Child2Id="11" Name="Dick" .../>
<Child2 Child2Id="12" Name="Harry" .../>
The query looks something like
SELECT
1 AS Tag
, NULL AS Parent
, header AS 'Header!1!Id'
::
::
, NULL AS 'Child1!2!Child1Id'
, NULL AS 'Child1!2!Name'
, NULL AS 'Child2!3!Child1Id'
, NULL AS 'Child2!3!Name'
FROM
blah, blah, blah
UNION ALL
SELECT
1 AS Tag
, NULL AS Parent
, NULL AS 'Header!1!Id'
::
::
, ChildId AS 'Child1!2!Child1Id'
, Name AS 'Child1!2!Name'
, NULL AS 'Child2!3!Child1Id'
, NULL AS 'Child2!3!Name'
FROM
blah, blah, blah
UNION ALL
SELECT
1 AS Tag
, NULL AS Parent
, NULL AS 'Header!1!Id'
::
::
, NULL AS 'Child1!2!Child1Id'
, NULL AS 'Child1!2!Name'
, ChildId AS 'Child2!3!Child1Id'
, Name AS 'Child2!3!Name'
FROM
blah, blah, blah
Any help with either of these is gratefully appreciated.
Let me know if I am on the wrong path also, as I wouldn't be surprised
Thanks
SteveYou're missing an ORDER BY. Try this
create table #Header(HeaderId int)
insert into #Header(HeaderId) values(1)
insert into #Header(HeaderId) values(2)
insert into #Header(HeaderId) values(3)
create table #Child1(HeaderId int,Child1Id int,Name varchar(5))
create table #Child2(HeaderId int,Child2Id int,Name varchar(5))
insert into #Child1(HeaderId,Child1Id,Name) values(1,1,'Fred')
insert into #Child1(HeaderId,Child1Id,Name) values(1,2,'Tom')
insert into #Child2(HeaderId,Child2Id,Name) values(1,3,'Dick')
insert into #Child2(HeaderId,Child2Id,Name) values(1,4,'Harry')
insert into #Child1(HeaderId,Child1Id,Name) values(2,5,'Fred')
insert into #Child1(HeaderId,Child1Id,Name) values(2,6,'Tom')
insert into #Child2(HeaderId,Child2Id,Name) values(2,7,'Dick')
insert into #Child2(HeaderId,Child2Id,Name) values(2,8,'Harry')
insert into #Child1(HeaderId,Child1Id,Name) values(3,9,'Fred')
insert into #Child1(HeaderId,Child1Id,Name) values(3,10,'Tom')
insert into #Child2(HeaderId,Child2Id,Name) values(3,11,'Dick')
insert into #Child2(HeaderId,Child2Id,Name) values(3,12,'Harry')
SELECT 1 AS Tag
, NULL AS Parent
, h.HeaderId AS 'Header!1!Id'
, NULL AS 'Child1!2!Child1Id'
, NULL AS 'Child1!2!Name'
, NULL AS 'Child2!3!Child1Id'
, NULL AS 'Child2!3!Name'
FROM #Header h
UNION ALL
SELECT 2 AS Tag
, 1 AS Parent
, h.HeaderId AS 'Header!1!Id'
, c.Child1Id AS 'Child1!2!Child1Id'
, c.Name AS 'Child1!2!Name'
, NULL AS 'Child2!3!Child1Id'
, NULL AS 'Child2!3!Name'
FROM #Header h
INNER JOIN #Child1 c ON c.HeaderId=h.HeaderId
UNION ALL
SELECT 3 AS Tag
, 1 AS Parent
, h.HeaderId AS 'Header!1!Id'
, NULL AS 'Child1!2!Child1Id'
, NULL AS 'Child1!2!Name'
, c.Child2Id AS 'Child2!3!Child1Id'
, c.Name AS 'Child2!3!Name'
FROM #Header h
INNER JOIN #Child2 c ON c.HeaderId=h.HeaderId
ORDER BY h.HeaderId,Tag
FOR XML EXPLICIT
drop table #Child2
drop table #Child1
drop table #Header|||
"markc600@.hotmail.com" wrote:

> You're missing an ORDER BY. Try this
>
> create table #Header(HeaderId int)
> insert into #Header(HeaderId) values(1)
> insert into #Header(HeaderId) values(2)
> insert into #Header(HeaderId) values(3)
> create table #Child1(HeaderId int,Child1Id int,Name varchar(5))
> create table #Child2(HeaderId int,Child2Id int,Name varchar(5))
> insert into #Child1(HeaderId,Child1Id,Name) values(1,1,'Fred')
> insert into #Child1(HeaderId,Child1Id,Name) values(1,2,'Tom')
> insert into #Child2(HeaderId,Child2Id,Name) values(1,3,'Dick')
> insert into #Child2(HeaderId,Child2Id,Name) values(1,4,'Harry')
> insert into #Child1(HeaderId,Child1Id,Name) values(2,5,'Fred')
> insert into #Child1(HeaderId,Child1Id,Name) values(2,6,'Tom')
> insert into #Child2(HeaderId,Child2Id,Name) values(2,7,'Dick')
> insert into #Child2(HeaderId,Child2Id,Name) values(2,8,'Harry')
> insert into #Child1(HeaderId,Child1Id,Name) values(3,9,'Fred')
> insert into #Child1(HeaderId,Child1Id,Name) values(3,10,'Tom')
> insert into #Child2(HeaderId,Child2Id,Name) values(3,11,'Dick')
> insert into #Child2(HeaderId,Child2Id,Name) values(3,12,'Harry')
> SELECT 1 AS Tag
> , NULL AS Parent
> , h.HeaderId AS 'Header!1!Id'
> , NULL AS 'Child1!2!Child1Id'
> , NULL AS 'Child1!2!Name'
> , NULL AS 'Child2!3!Child1Id'
> , NULL AS 'Child2!3!Name'
> FROM #Header h
> UNION ALL
> SELECT 2 AS Tag
> , 1 AS Parent
> , h.HeaderId AS 'Header!1!Id'
> , c.Child1Id AS 'Child1!2!Child1Id'
> , c.Name AS 'Child1!2!Name'
> , NULL AS 'Child2!3!Child1Id'
> , NULL AS 'Child2!3!Name'
> FROM #Header h
> INNER JOIN #Child1 c ON c.HeaderId=h.HeaderId
> UNION ALL
> SELECT 3 AS Tag
> , 1 AS Parent
> , h.HeaderId AS 'Header!1!Id'
> , NULL AS 'Child1!2!Child1Id'
> , NULL AS 'Child1!2!Name'
> , c.Child2Id AS 'Child2!3!Child1Id'
> , c.Name AS 'Child2!3!Name'
> FROM #Header h
> INNER JOIN #Child2 c ON c.HeaderId=h.HeaderId
> ORDER BY h.HeaderId,Tag
> FOR XML EXPLICIT
>
> drop table #Child2
> drop table #Child1
> drop table #Header
>
Excellent, so close yet so far.
Also, in my actual code I had not propogated the Header Id into the other
parts of the union, it din't actually know what the relationships were.
And now BizTalk seems to like it.
Thanks