More XML query questions

**pnkfloyd** · Apr 10 '07, 12:03 AM

I am so close. Now, if you can tell me how to do this part, I will be forever grateful and get out of your forum :)

I generate a resultset based on the values they have entered for the valid attribute names. This may get hairy, but you can see what I am doing. The columns in my resultset that get generated are dynamic based on the code below that makes a column with a value for each XmlFieldName.

Code:

ALTER PROCEDURE [dbo].[GenerateAddressesForExports]
(
	@AssociationID bigint
)
AS
	
CREATE TABLE #tblDynamicColumnNames 
( 
   XmlFieldName nvarchar(50) NOT NULL PRIMARY KEY 
) 

INSERT INTO #tblDynamicColumnNames 
	select XmlFieldName from DefaultAddressFieldDefs
	union all
	select XmlFieldName from CustomAddressFieldDefs ca
		where ca.AssociationId = @AssociationID
	
CREATE TABLE #tblDynamicColumnValues 
(    
   EntityID bigint NOT NULL,
   XmlFieldName nvarchar(50) NOT NULL,
   StringValue nvarchar(500) NOT NULL 
) 

INSERT INTO #tblDynamicColumnValues 
	select top 50 		
		a.EntityId,
		daf.XmlFieldName,
		af.StringValue
	from defaultaddressfielddefs daf
	inner join addressfields af on af.AddressFieldDefId = daf.AddressFieldDefId
	inner join addresses a on a.AddressId = af.Addressid
	inner join Membership m on m.EntityId = a.EntityId	
	where m.AssociationID = @AssociationId

DECLARE @pivotValues nvarchar(1000)
SELECT @pivotValues = ''

SELECT @pivotValues = @pivotValues + '[' + #tblDynamicColumnNames.XmlFieldName + '],' 
FROM #tblDynamicColumnNames
GROUP BY #tblDynamicColumnNames.XmlFieldName 

SELECT @pivotValues = LEFT(@pivotValues, LEN(@pivotValues) - 1) 	

DECLARE @sqlString nvarchar(1000)
[B]SELECT @sqlString = 'select * from #tblDynamicColumnValues PIVOT (MAX(StringValue) FOR XmlFieldName IN (' + @pivotValues + ')) AS InfoPivot for xml raw(''Address''), ROOT(''Addresses''), TYPE'[/B]
EXEC (@sqlString)

drop table #tblDynamicColumnValues
drop table #tblDynamicColumnNames

The output that gets produced is (notice how is uses whatever I have in the DB for the XmlFieldName for each attribute)

Code:

<Addresses>
  <Address EntityID="22619" City="Conroe" Country="" CountryCode="" State="TX" StreetAddress1="3205 W. Davis, Sp Ed Dept" StreetAddress2="Special Education Dept." StreetAddress3="" ZipCode="77304" />
  <Address EntityID="22620" City="Kermit" Country="" CountryCode="" State="TX" StreetAddress1="601 South Poplar" StreetAddress2="" StreetAddress3="" ZipCode="79745" />
  <Address EntityID="22621" City="Pleasanton" Country="" CountryCode="" State="TX" StreetAddress1="831 Stadium Drive" StreetAddress2="" StreetAddress3="" ZipCode="78064" />
  <Address EntityID="22622" StreetAddress1="920 Burke" StreetAddress2="" />
</Addresses>

Now the EASY party for you probably but it has stumped me so far. How can I make my sproc return the xml in a variable so that when I do this, @myXML will have my XML in there?

DECLARE @myXML xml
exec <sproc> <someid>, @myXML

select @myXML

thanks!

Brian

Originally posted by iburyak

Even without XML just a select statement from a table to pivot you need to know all expected values and it is never dynamic.
Where did you see a query with dynamic number of columns in select statement and each row having different number of columns?
The only thing that can be done here is mock XML where we can concatenate all the elements and send each as a separate row.
Like this:
[PHP]<Addresses>
<Address EntityID = 1 City="Austin" />
<Address EntityID = 1 State="TX" />
<Address EntityID = 1 Country="US"/>

<Address EntityID = 2 CityName="Los Angeles" />
<Address EntityID = 2 StateCode="TX" />
<Address EntityID = 2 CountryCode="US " Zip="111111"/>
</Addresses>[/PHP]

**iburyak** · Apr 10 '07, 12:22 AM

Try this:

[PHP]DECLARE @myXML xml

exec <sproc> <someid>, @myXML output

select @myXML[/PHP]

**iburyak** · Apr 10 '07, 06:14 AM

Originally posted by iburyak

Try this:

[PHP]DECLARE @myXML xml

exec <sproc> <someid>, @myXML output

select @myXML[/PHP]

I take my words back. It is not as simple as above.

See my comments below:

[PHP]ALTER PROCEDURE [dbo].[GenerateAddress esForExports]
(
@AssociationID bigint,
@myXML xml output

AS

CREATE TABLE #tblDynamicColu mnNames
(
XmlFieldName nvarchar(50) NOT NULL PRIMARY KEY
)

INSERT INTO #tblDynamicColu mnNames
select XmlFieldName from DefaultAddressF ieldDefs
union all
select XmlFieldName from CustomAddressFi eldDefs ca
where ca.AssociationI d = @AssociationID

CREATE TABLE #tblDynamicColu mnValues
(
EntityID bigint NOT NULL,
XmlFieldName nvarchar(50) NOT NULL,
StringValue nvarchar(500) NOT NULL
)

INSERT INTO #tblDynamicColu mnValues
select top 50
a.EntityId,
daf.XmlFieldNam e,
af.StringValue
from defaultaddressf ielddefs daf
inner join addressfields af on af.AddressField DefId = daf.AddressFiel dDefId
inner join addresses a on a.AddressId = af.Addressid
inner join Membership m on m.EntityId = a.EntityId
where m.AssociationID = @AssociationId

DECLARE @pivotValues nvarchar(1000)
SELECT @pivotValues = ''

SELECT @pivotValues = @pivotValues + '[' + #tblDynamicColu mnNames.XmlFiel dName + '],'
FROM #tblDynamicColu mnNames
GROUP BY #tblDynamicColu mnNames.XmlFiel dName

SELECT @pivotValues = LEFT(@pivotValu es, LEN(@pivotValue s) - 1)

DECLARE @sqlString nvarchar(1000)
SELECT @sqlString = 'select * from #tblDynamicColu mnValues PIVOT (MAX(StringValu e) FOR XmlFieldName IN (' + @pivotValues + ')) AS InfoPivot for xml raw(''Address'' ), ROOT(''Addresse s''), TYPE'
EXEC (@sqlString)

--- Here probably is a good Idea to take result of EXEC (@sqlString) statement into a temp table then loop through all records and concatenate all records into one
--- string then return that result to @myXML variable. This is what I meant by saying that you can create mock XML.

drop table #tblDynamicColu mnValues
drop table #tblDynamicColu mnNames

[/PHP]

**pnkfloyd** · Apr 10 '07, 02:59 PM

Thanks!

I am not sure I understand when you say to loop though the results from the EXEC. Using a cursor and removing the FOR XML RAW?

Currently, the EXEC(@sqlString ) call in my stored proc runs that dynamic sql with the FOR XML RAW call, which gives me back a resultset with just 1 row and column containing the results as the XML type. How do I take that and return it as an output parameter? I have tried quite a bit but nothing works. The BEST I have come up with is this (below), but I would like it all contained in the SPROC and return the XML. One more option (that I prefer) is to return my result set from the SPROC as a table (and remove the FOR XML RAW) but the problem is that I can't declare the table because I create the result set dynamically and I don't know the number of columns or the column names in advance.

Code:

DECLARE @xmlResults table
(
	resultsXml xml
)

insert @xmlResults
	exec [GenerateAddressesForExports] 2499

select * from @xmlResults

Once again, thanks SOOO much. YOu have no idea how much you have helped.

Brian

Originally posted by iburyak

I take my words back. It is not as simple as above.

See my comments below:

[PHP]ALTER PROCEDURE [dbo].[GenerateAddress esForExports]
(
@AssociationID bigint,
@myXML xml output

AS

CREATE TABLE #tblDynamicColu mnNames
(
XmlFieldName nvarchar(50) NOT NULL PRIMARY KEY
)

INSERT INTO #tblDynamicColu mnNames
select XmlFieldName from DefaultAddressF ieldDefs
union all
select XmlFieldName from CustomAddressFi eldDefs ca
where ca.AssociationI d = @AssociationID

CREATE TABLE #tblDynamicColu mnValues
(
EntityID bigint NOT NULL,
XmlFieldName nvarchar(50) NOT NULL,
StringValue nvarchar(500) NOT NULL
)

INSERT INTO #tblDynamicColu mnValues
select top 50
a.EntityId,
daf.XmlFieldNam e,
af.StringValue
from defaultaddressf ielddefs daf
inner join addressfields af on af.AddressField DefId = daf.AddressFiel dDefId
inner join addresses a on a.AddressId = af.Addressid
inner join Membership m on m.EntityId = a.EntityId
where m.AssociationID = @AssociationId

DECLARE @pivotValues nvarchar(1000)
SELECT @pivotValues = ''

SELECT @pivotValues = @pivotValues + '[' + #tblDynamicColu mnNames.XmlFiel dName + '],'
FROM #tblDynamicColu mnNames
GROUP BY #tblDynamicColu mnNames.XmlFiel dName

SELECT @pivotValues = LEFT(@pivotValu es, LEN(@pivotValue s) - 1)

DECLARE @sqlString nvarchar(1000)
SELECT @sqlString = 'select * from #tblDynamicColu mnValues PIVOT (MAX(StringValu e) FOR XmlFieldName IN (' + @pivotValues + ')) AS InfoPivot for xml raw(''Address'' ), ROOT(''Addresse s''), TYPE'
EXEC (@sqlString)

--- Here probably is a good Idea to take result of EXEC (@sqlString) statement into a temp table then loop through all records and concatenate all records into one
--- string then return that result to @myXML variable. This is what I meant by saying that you can create mock XML.

drop table #tblDynamicColu mnValues
drop table #tblDynamicColu mnNames

[/PHP]

**iburyak** · Apr 10 '07, 03:34 PM

I thought of something else.

You know I have no way of testing it so try it with thought in mind it can give you an error message. But hope you'll get an idea.

Code:

ALTER PROCEDURE [dbo].[GenerateAddressesForExports]
(
    @AssociationID bigint,
        @myXML xml output     

AS
    
CREATE TABLE #tblDynamicColumnNames 
( 
   XmlFieldName nvarchar(50) NOT NULL PRIMARY KEY 
) 

INSERT INTO #tblDynamicColumnNames 
    select XmlFieldName from DefaultAddressFieldDefs
    union all
    select XmlFieldName from CustomAddressFieldDefs ca
        where ca.AssociationId = @AssociationID
    
CREATE TABLE #tblDynamicColumnValues 
(    
   EntityID bigint NOT NULL,
   XmlFieldName nvarchar(50) NOT NULL,
   StringValue nvarchar(500) NOT NULL 
) 

INSERT INTO #tblDynamicColumnValues 
    select top 50         
        a.EntityId,
        daf.XmlFieldName,
        af.StringValue
    from defaultaddressfielddefs daf
    inner join addressfields af on af.AddressFieldDefId = daf.AddressFieldDefId
    inner join addresses a on a.AddressId = af.Addressid
    inner join Membership m on m.EntityId = a.EntityId    
    where m.AssociationID = @AssociationId

DECLARE @pivotValues nvarchar(1000)
SELECT @pivotValues = ''

SELECT @pivotValues = @pivotValues + '[' + #tblDynamicColumnNames.XmlFieldName + '],' 
FROM #tblDynamicColumnNames
GROUP BY #tblDynamicColumnNames.XmlFieldName 

SELECT @pivotValues = LEFT(@pivotValues, LEN(@pivotValues) - 1)     

DECLARE @sqlString nvarchar(1000)
SELECT @sqlString = 'select * from #tblDynamicColumnValues PIVOT (MAX(StringValue) FOR XmlFieldName IN (' + @pivotValues + ')) AS InfoPivot for xml raw(''Address''), ROOT(''Addresses''), TYPE'
--EXEC (@sqlString)

exec sp_executesql @sqlString, N'@RetXML XML output', @RetXML = @myXML output


drop table #tblDynamicColumnValues
drop table #tblDynamicColumnNames

**pnkfloyd** · Apr 10 '07, 08:26 PM

I had tried that, various ways. I cannot get it to work. I literally have tried everything. I am about to jump out of my office window :)

The closest I came was this, which gives me back the results in a table, but it isn't what I need.

Code:

DECLARE @xmlResults table
(
	resultsXml xml
)

insert @xmlResults
	exec [GenerateAddressesForExports] 2499

I have also tried this, which is CLOSE, but WAAAAY too slow

The first part gives me back something like

EntityID Attribute
1 Street="Whateve r"
1 City="Blah"

I then go through each EntityID and concat the attributes. Is there anyway to combine those rows without cursors?

it is too slow. I am stumped, and no further along then I was 2 days ago :(

Also, ignore the colorful table variable name :)

Code:

DECLARE @fuckThisShit TABLE
(
	EntityId bigint,
	Attribute nvarchar(max)	
) 

DECLARE @resultsTable TABLE
(
	EntityId bigint,
	Attributes nvarchar(max)	
) 

DECLARE @IsFirst bit
DECLARE @EntityId bigint
DECLARE @CurrentEntityId bigint
DECLARE @Attribute nvarchar(max)
DECLARE @attributes nvarchar(max)

-- grab all our data!
insert into @fuckThisShit
	SELECT a.EntityId, cast ( daf.XmlFieldName + '="' + af.StringValue + '" ' as nvarchar(max))
	from defaultaddressfielddefs daf
		inner join addressfields af on af.AddressFieldDefId = daf.AddressFieldDefId
		inner join addresses a on a.AddressId = af.AddressId
		inner join membership m on a.EntityId = m.EntityId
	where af.StringValue <> ''	

--select * from @fuckThisShit 

-- begin cursor crap!

SET @CurrentEntityId = -1
SET @attributes = ''

DECLARE c1 CURSOR READ_ONLY FOR
	select EntityId, Attribute from @fuckThisShit
	
OPEN c1

FETCH NEXT FROM c1 INTO @EntityId, @Attribute
SET @CurrentEntityId = @EntityId

WHILE @@FETCH_STATUS = 0
BEGIN		
	
	SET @attributes = @attributes + ' ' + @Attribute	
					
	FETCH NEXT FROM c1 INTO @EntityId, @Attribute	

	IF ( @EntityID > @CurrentEntityId ) 
	BEGIN		
		insert into @resultsTable ( EntityId, Attributes )	VALUES ( @CurrentEntityId, @attributes )		
		SET @attributes = ''									
		SET @CurrentEntityId = @EntityID				
	END	
END
	
insert into @resultsTable ( EntityId, Attributes )	VALUES ( @CurrentEntityId, @attributes )	

CLOSE c1
DEALLOCATE c1


select * from @resultsTable

Originally posted by iburyak

I thought of something else.

You know I have no way of testing it so try it with thought in mind it can give you an error message. But hope you'll get an idea.

Code:

ALTER PROCEDURE [dbo].[GenerateAddressesForExports]
(
    @AssociationID bigint,
        @myXML xml output     

AS
    
CREATE TABLE #tblDynamicColumnNames 
( 
   XmlFieldName nvarchar(50) NOT NULL PRIMARY KEY 
) 

INSERT INTO #tblDynamicColumnNames 
    select XmlFieldName from DefaultAddressFieldDefs
    union all
    select XmlFieldName from CustomAddressFieldDefs ca
        where ca.AssociationId = @AssociationID
    
CREATE TABLE #tblDynamicColumnValues 
(    
   EntityID bigint NOT NULL,
   XmlFieldName nvarchar(50) NOT NULL,
   StringValue nvarchar(500) NOT NULL 
) 

INSERT INTO #tblDynamicColumnValues 
    select top 50         
        a.EntityId,
        daf.XmlFieldName,
        af.StringValue
    from defaultaddressfielddefs daf
    inner join addressfields af on af.AddressFieldDefId = daf.AddressFieldDefId
    inner join addresses a on a.AddressId = af.Addressid
    inner join Membership m on m.EntityId = a.EntityId    
    where m.AssociationID = @AssociationId

DECLARE @pivotValues nvarchar(1000)
SELECT @pivotValues = ''

SELECT @pivotValues = @pivotValues + '[' + #tblDynamicColumnNames.XmlFieldName + '],' 
FROM #tblDynamicColumnNames
GROUP BY #tblDynamicColumnNames.XmlFieldName 

SELECT @pivotValues = LEFT(@pivotValues, LEN(@pivotValues) - 1)     

DECLARE @sqlString nvarchar(1000)
SELECT @sqlString = 'select * from #tblDynamicColumnValues PIVOT (MAX(StringValue) FOR XmlFieldName IN (' + @pivotValues + ')) AS InfoPivot for xml raw(''Address''), ROOT(''Addresses''), TYPE'
--EXEC (@sqlString)

exec sp_executesql @sqlString, N'@RetXML XML output', @RetXML = @myXML output


drop table #tblDynamicColumnValues
drop table #tblDynamicColumnNames

**iburyak** · Apr 10 '07, 09:21 PM

I think I see what is a problem.

You said this statement below returns one XML streeng.
Is this true? I have no way of testing it.

select * from #tblDynamicColu mnValues PIVOT (MAX(StringValu e) FOR XmlFieldName IN (' + @pivotValues + ')) AS InfoPivot for xml raw(''Address'' ), ROOT(''Addresse s''), TYPE

You have to somehow return result into this variable
Like

Select @RetXML =

See working example that I created:
1. Create test procedure:

Code:

Create proc TestXML
@myXML varchar(1000) output
AS
DECLARE @sqlString nvarchar(1000)
SELECT @sqlString = 'select @RetXML = ''<Root><Address attr=Value12/></Root>'''

exec sp_executesql @sqlString, N'@RetXML varchar(1000) output',  @RetXML=@myXML output

2. Execute procedure:

Code:

Declare @myXML varchar(1000)
exec TestXML @myXML output
Select @myXML

Good Luck.
I feel your pain.... :)

**pnkfloyd** · Apr 11 '07, 04:17 PM

Thanks SOOOO much for your help. You have been so patient. I wish I could buy you a beer or something.

Anyway, here is the final solution if anyone was crazy enough to follow this thread :)

Code:

create function UDF_GetAddressAsXML (@AddressID int)
returns nvarchar(max)
as
begin

	DECLARE @xml NVARCHAR(MAX)
	SET @xml = '<Address '

	SELECT @xml = @xml + 'AddressType="' + cat.Description + '" '
	FROM CustomAddressTypes cat inner join 
	Addresses a on a.AddressTypeId = cat.AddressTypeId
	where a.AddressId = @AddressId

	SELECT @xml = @xml + REPLACE(XmlFieldName, ' ', '') + '="' + StringValue + '" ' 
	FROM AddressFields af
	INNER JOIN DefaultAddressFieldDefs dafd ON af.AddressFieldDefID=dafd.AddressFieldDefID
	where AddressId = @AddressId

	SET @xml = @xml + ' />'
	return @xml 

end


GO

create function UDF_GetEntityAddressesAsXML (@EntityID int)
returns xml
as
begin

	declare @xml nvarchar(max)

	set @xml = '<Addresses>' 
	select @xml = @xml + dbo.UDF_GetAddressAsXML(a.AddressId) from
		Addresses a where a.EntityId = @EntityID
	set @xml = @xml + '</Addresses>'
	
	return CAST(@xml as xml) 
end

GO

SELECT dbo.UDF_GetEntityAddressesAsXML(13)

GO

More XML query questions

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment