473,324 Members | 2,248 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

More XML query questions

Can someone explain why this won't work? It works if I take out the first "{sql:column("daf.XmlFieldName")}" and replace is with some hard coded string. I need this to be dynamic based on that XmlFieldName field value

Thanks!!
Brian

Expand|Select|Wrap|Line Numbers
  1. DECLARE @myXML xml
  2. SET @myXML = ''
  3.  
  4. SELECT top 1 daf.XmlFieldName, af.StringValue, @myXML.query('               
  5.         <Address
  6.             "{sql:column("daf.XmlFieldName")}"="{sql:column("af.StringValue")}"            
  7.         />')
  8.     AS Result
  9. from defaultaddressfielddefs daf
  10. inner join addressfields af on af.AddressFieldDefId = daf.AddressFieldDefId
  11. where af.StringValue <> ''
  12.  
Apr 5 '07 #1
22 2908
Does anyone have any suggestions for my question below???

Thanks!
Brian

Can someone explain why this won't work? It works if I take out the first "{sql:column("daf.XmlFieldName")}" and replace is with some hard coded string. I need this to be dynamic based on that XmlFieldName field value

Thanks!!
Brian

Expand|Select|Wrap|Line Numbers
  1. DECLARE @myXML xml
  2. SET @myXML = ''
  3.  
  4. SELECT top 1 daf.XmlFieldName, af.StringValue, @myXML.query('               
  5.         <Address
  6.             "{sql:column("daf.XmlFieldName")}"="{sql:column("af.StringValue")}"            
  7.         />')
  8.     AS Result
  9. from defaultaddressfielddefs daf
  10. inner join addressfields af on af.AddressFieldDefId = daf.AddressFieldDefId
  11. where af.StringValue <> ''
  12.  
Apr 9 '07 #2
iburyak
1,017 Expert 512MB
I gave you possible work around which would return following XML but you didn't seem to be interested.

[PHP]<ROOT>
<Default name="Pepsi" value="Free">
<Default name="Coke" vlue="Zero">
</ROOT> [/PHP]

Unfortunately I believe what ever you are trying to achieve will make XML not well formed because it will not be able to built static Schema where each attribute defined in advance and this is a reason it is not possible.
Apr 9 '07 #3
I am very interested. Unfortunately, I have to validate against an XSD and it looks something like below. We have a weird case where we generate that part of the XSD so that our customers cannot put attributes in there that don't map to fields in our DB. The attributes are dynamically generated, so this list of attrs below can change based on if they have added address fields in our application. Therefore, as much as I would like to, I can't do your suggestion.

I am going down the path now of generating a table that has the dynamically generated rows and using a PIVOT table?? I have never done anything like that, but I think it could work?

I REALLY appreciate your help. Does this example make things clearer or give you any more ideas?

Thanks!

<xs:element name="Addresses">
<xs:complexType>
<xs:sequence maxOccurs="unbounded">
<xs:element name="Address">
<xs:complexType>
<xs:attribute name="AddressType" type="xs:string" use="required" />
<xs:attribute name="City" type="xs:string" />
<xs:attribute name="Country" type="xs:string" />
<xs:attribute name="CountryCode" type="xs:string" />
<xs:attribute name="State" type="xs:string" />
<xs:attribute name="StreetAddress1" type="xs:string" />
<xs:attribute name="StreetAddress2" type="xs:string" />
<xs:attribute name="StreetAddress3" type="xs:string" />
<xs:attribute name="ZipCode" type="xs:string" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>




I gave you possible work around which would return following XML but you didn't seem to be interested.

[PHP]<ROOT>
<Default name="Pepsi" value="Free">
<Default name="Coke" vlue="Zero">
</ROOT> [/PHP]

Unfortunately I believe what ever you are trying to achieve will make XML not well formed because it will not be able to built static Schema where each attribute defined in advance and this is a reason it is not possible.
Apr 9 '07 #4
iburyak
1,017 Expert 512MB
As I can tell, you are looking for SQL query to generate following XML, which is easy to do. ln your original question you wanted to generate each attribute name dynamically which is not possible.

[PHP]
<Addresses>
<Address AddressType = “” City =”” Country =”” CountryCode=”” State=”” StreetAddress1=”” StreetAddress2=”” StreetAddress3=”” ZipCode =””>
</Addresses>
[/PHP]

If you want to generate something like above give me column names and data sample so I will be able to do it for you.
Apr 9 '07 #5
Yes, close. I am looking actually for this

<Addresses>
<Address AddressType="<some value>" City=<some value> Country=<some value>"... />
</Addresses>

Please note, though, that the attribute names (City, Country, etc) are to be named from a value in the XmlFieldName column for each row. So, it may NOT be called City, it may be called CityName, for instance. That is what I mean that it is dynamic and that I can't simply do (psuedo code)

select
XmlFieldName as '@City'
from whatever
for xml path


thanks!


As I can tell, you are looking for SQL query to generate following XML, which is easy to do. ln your original question you wanted to generate each attribute name dynamically which is not possible.

[PHP]
<Addresses>
<Address AddressType = “” City =”” Country =”” CountryCode=”” State=”” StreetAddress1=”” StreetAddress2=”” StreetAddress3=”” ZipCode =””>
</Addresses>
[/PHP]

If you want to generate something like above give me column names and data sample so I will be able to do it for you.
Apr 9 '07 #6
Oh, so basically that is why I can't send you the column names, because there could be 3 or 10 or whatever ... that was what I meant by dynamic.




Yes, close. I am looking actually for this

<Addresses>
<Address AddressType="<some value>" City=<some value> Country=<some value>"... />
</Addresses>

Please note, though, that the attribute names (City, Country, etc) are to be named from a value in the XmlFieldName column for each row. So, it may NOT be called City, it may be called CityName, for instance. That is what I mean that it is dynamic and that I can't simply do (psuedo code)

select
XmlFieldName as '@City'
from whatever
for xml path


thanks!
Apr 9 '07 #7
iburyak
1,017 Expert 512MB
I don't understand you.
Do you have a table? How many columns in it?
Even if you have data like this

ColumnName value
Address1 444 some street
Zip 888888


It should still generate only known values and the rest will be "“ like Address2= “”
This way number of attributes will be always the same but available data will be always different but if you want to create address element with only 2 attributes and according to your schema it expects 10 it will never happen.

Thank you.
Apr 9 '07 #8
The table is basically a lookup. For instance

We have an entity, lets say entityID

Then we have a table

EntityID ColumnName Value
1 City Austin
1 State TX
1 Street1
1



I don't understand you.
Do you have a table? How many columns in it?
Even if you have data like this

ColumnName value
Address1 444 some street
Zip 888888


It should still generate only known values and the rest will be "“ like Address2= “”
This way number of attributes will be always the same but available data will be always different but if you want to create address element with only 2 attributes and according to your schema it expects 10 it will never happen.

Thank you.
Apr 9 '07 #9
The table is basically a lookup. For instance

We have an entity, lets say entityID with value of 1

Then we have a table

EntityID XmlFieldName Value
1 City Austin
1 State TX
1 Street1 1234 Memory lane


This table can have 0 to n rows for that entity, meaning, his address can be multiple fields. The XmlFieldName(s) is set in the application, so it can be different AND it varies on the number of rows returned for a particular entity. In the above dataset, the address would only generate 3 attributes in the xml like:

<Address City="Austin" State="TX" Street1="1234 Memory Lane"/>

but if they added another field, for instance Country with a value of US (through the application), it should be

<Address City="Austin" State="TX" Street1="1234 Memory Lane" Country="US"/>

Notice how the attributes are based on the actual XMLFieldName value of a particular row.


This forces them to only enter attributes that we can handle and that is how our XSD is set up. Keep in mind, the XSD is augmented in code to add the attributes that a particular entity uses. So, for the case above, it would be generated as:

- <xs:element name="Addresses">
- <xs:complexType>
- <xs:sequence maxOccurs="unbounded">
- <xs:element name="Address">
- <xs:complexType>
<xs:attribute name="City" type="xs:string" />
<xs:attribute name="Country" type="xs:string" />
<xs:attribute name="State" type="xs:string" />
<xs:attribute name="Street1" type="xs:string" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>


I really appreciate your patience! Thanks so much!
Brian


I don't understand you.
Do you have a table? How many columns in it?
Even if you have data like this

ColumnName value
Address1 444 some street
Zip 888888


It should still generate only known values and the rest will be "“ like Address2= “”
This way number of attributes will be always the same but available data will be always different but if you want to create address element with only 2 attributes and according to your schema it expects 10 it will never happen.

Thank you.
Apr 9 '07 #10
iburyak
1,017 Expert 512MB
OK

1. Create test table:

[PHP]create table Personal (
EntityID int,
ColumnName varchar(50),
Value varchar(50))[/PHP]

2. Insert test data:
[PHP]insert into Personal values (1, 'City', 'Austin')
insert into Personal values (1, 'State', 'TX')
insert into Personal values (1, 'StreetAddress1', '222 some street')
insert into Personal values (2, 'City', 'Austin')
insert into Personal values (2, 'State', 'TX')
insert into Personal values (2, 'StreetAddress1', '333 some street')
insert into Personal values (2, 'ZipCode', '666666') [/PHP]

3. Execute following query:

[PHP]select 1 TAG,
null Parent,
'' [Addresses!1],
null [Address!2!AddressType],
null [Address!2!City],
null [Address!2!Country],
null [Address!2!CountryCode],
null [Address!2!State],
null [Address!2!StreetAddress1],
null [Address!2!StreetAddress2],
null [Address!2!StreetAddress3],
null [Address!2!ZipCode]
UNION ALL
select 2 TAG,
1 Parent,
null,
max(case when ColumnName = 'AddressType' then Value else '' end) [Address!2!AddressType],
max(case when ColumnName = 'City' then Value else '' end) [Address!2!City],
max(case when ColumnName = 'Country' then Value else '' end) [Address!2!Country],
max(case when ColumnName = 'CountryCode' then Value else '' end) [Address!2!CountryCode],
max(case when ColumnName = 'State' then Value else '' end) [Address!2!State],
max(case when ColumnName = 'StreetAddress1' then Value else '' end) [Address!2!StreetAddress1],
max(case when ColumnName = 'StreetAddress2' then Value else '' end) [Address!2!StreetAddress2],
max(case when ColumnName = 'StreetAddress3' then Value else '' end) [Address!2!StreetAddress3],
max(case when ColumnName = 'ZipCode' then Value else '' end) [Address!2!ZipCode]
from Personal
group by EntityID
FOR XML EXPLICIT[/PHP]

4. Result should be :

[PHP]<Addresses>
<Address AddressType="" City="Austin" Country="" CountryCode="" State="TX" StreetAddress1="222 some street" StreetAddress2="" StreetAddress3="" ZipCode=""/>
<Address AddressType="" City="Austin" Country="" CountryCode="" State="TX" StreetAddress1="333 some street" StreetAddress2="" StreetAddress3="" ZipCode="666666"/>
</Addresses>[/PHP]

Good Luck.
Apr 9 '07 #11
I REALLY appreciate your help, but we are on different pages.

I cannot hard code City, Country, State, etc anywhere. You example assumes the name of the attribute, specifying [Address!2!City] etc. You hard code City. But THAT 'City' attribute name needs to be based on a value in the DB. The XmlFieldName in the DB can be 'CityName' and in that case, I need the attribute to be CityName.

Here is an example for TWO different entities

EntityID XmlFieldName Value
1 City Austin
1 State TX
1 Country US
2 CityName Los Angeles
2 StateCode CA
2 CountryCode US
2 Zip 111111

Here is how the two different results should be

where EntityID = 1
<Address City="Austin" State="TX" Country="US"/>

where EntityID = 2
<Address CityName="Los Angeles" StateCode="TX" CountryCode="US" Zip="111111"/>

Notice how the attribute names (City or CityName, State or StateCode, etc) are based off the XmlFieldName and I don't know in advance what the possible values will be?

Thanks! I think we are close! Stick with me :)

Brian



I do not know what the value in ColumnName will be and, as such, need to generate it based on what value is in there and there can be 20 rows as easily as 10 rows, depending on how a country sets up their address fields. Therefore, I can't do that case statement.

I wish we could chat over IM or something so I could explain this better. Is that an option?

Thanks so much

Brian









OK

1. Create test table:

[PHP]create table Personal (
EntityID int,
ColumnName varchar(50),
Value varchar(50))[/PHP]

2. Insert test data:
[PHP]insert into Personal values (1, 'City', 'Austin')
insert into Personal values (1, 'State', 'TX')
insert into Personal values (1, 'StreetAddress1', '222 some street')
insert into Personal values (2, 'City', 'Austin')
insert into Personal values (2, 'State', 'TX')
insert into Personal values (2, 'StreetAddress1', '333 some street')
insert into Personal values (2, 'ZipCode', '666666') [/PHP]

3. Execute following query:

[PHP]select 1 TAG,
null Parent,
'' [Addresses!1],
null [Address!2!AddressType],
null [Address!2!City],
null [Address!2!Country],
null [Address!2!CountryCode],
null [Address!2!State],
null [Address!2!StreetAddress1],
null [Address!2!StreetAddress2],
null [Address!2!StreetAddress3],
null [Address!2!ZipCode]
UNION ALL
select 2 TAG,
1 Parent,
null,
max(case when ColumnName = 'AddressType' then Value else '' end) [Address!2!AddressType],
max(case when ColumnName = 'City' then Value else '' end) [Address!2!City],
max(case when ColumnName = 'Country' then Value else '' end) [Address!2!Country],
max(case when ColumnName = 'CountryCode' then Value else '' end) [Address!2!CountryCode],
max(case when ColumnName = 'State' then Value else '' end) [Address!2!State],
max(case when ColumnName = 'StreetAddress1' then Value else '' end) [Address!2!StreetAddress1],
max(case when ColumnName = 'StreetAddress2' then Value else '' end) [Address!2!StreetAddress2],
max(case when ColumnName = 'StreetAddress3' then Value else '' end) [Address!2!StreetAddress3],
max(case when ColumnName = 'ZipCode' then Value else '' end) [Address!2!ZipCode]
from Personal
group by EntityID
FOR XML EXPLICIT[/PHP]

4. Result should be :

[PHP]<Addresses>
<Address AddressType="" City="Austin" Country="" CountryCode="" State="TX" StreetAddress1="222 some street" StreetAddress2="" StreetAddress3="" ZipCode=""/>
<Address AddressType="" City="Austin" Country="" CountryCode="" State="TX" StreetAddress1="333 some street" StreetAddress2="" StreetAddress3="" ZipCode="666666"/>
</Addresses>[/PHP]

Good Luck.
Apr 9 '07 #12
I REALLY appreciate your help, but we are on different pages.

I cannot hard code City, Country, State, etc anywhere. You example assumes the name of the attribute, specifying [Address!2!City] etc. You hard code City. But THAT 'City' attribute name needs to be based on a value in the DB. The XmlFieldName in the DB can be 'CityName' and in that case, I need the attribute to be CityName.

Here is an example for TWO different entities

EntityID XmlFieldName Value
1 City Austin
1 State TX
1 Country US
2 CityName Los Angeles
2 StateCode CA
2 CountryCode US
2 Zip 111111

Here is how the two different results should be

where EntityID = 1
<Address City="Austin" State="TX" Country="US"/>

where EntityID = 2
<Address CityName="Los Angeles" StateCode="TX" CountryCode="US" Zip="111111"/>

Notice how the attribute names (City or CityName, State or StateCode, etc) are based off the XmlFieldName and I don't know in advance what the possible values will be?

Thanks! I think we are close! Stick with me :)

I wish we could chat over IM or something so I could explain this better. Is that an option?

Thanks so much

Brian
Apr 9 '07 #13
iburyak
1,017 Expert 512MB
I understand what you mean but I don't know how to make ti work.


Sorry.
Apr 9 '07 #14
iburyak
1,017 Expert 512MB
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]
Apr 9 '07 #15
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.

Expand|Select|Wrap|Line Numbers
  1. ALTER PROCEDURE [dbo].[GenerateAddressesForExports]
  2. (
  3.     @AssociationID bigint
  4. )
  5. AS
  6.  
  7. CREATE TABLE #tblDynamicColumnNames 
  8.    XmlFieldName nvarchar(50) NOT NULL PRIMARY KEY 
  9.  
  10. INSERT INTO #tblDynamicColumnNames 
  11.     select XmlFieldName from DefaultAddressFieldDefs
  12.     union all
  13.     select XmlFieldName from CustomAddressFieldDefs ca
  14.         where ca.AssociationId = @AssociationID
  15.  
  16. CREATE TABLE #tblDynamicColumnValues 
  17. (    
  18.    EntityID bigint NOT NULL,
  19.    XmlFieldName nvarchar(50) NOT NULL,
  20.    StringValue nvarchar(500) NOT NULL 
  21.  
  22. INSERT INTO #tblDynamicColumnValues 
  23.     select top 50         
  24.         a.EntityId,
  25.         daf.XmlFieldName,
  26.         af.StringValue
  27.     from defaultaddressfielddefs daf
  28.     inner join addressfields af on af.AddressFieldDefId = daf.AddressFieldDefId
  29.     inner join addresses a on a.AddressId = af.Addressid
  30.     inner join Membership m on m.EntityId = a.EntityId    
  31.     where m.AssociationID = @AssociationId
  32.  
  33. DECLARE @pivotValues nvarchar(1000)
  34. SELECT @pivotValues = ''
  35.  
  36. SELECT @pivotValues = @pivotValues + '[' + #tblDynamicColumnNames.XmlFieldName + '],' 
  37. FROM #tblDynamicColumnNames
  38. GROUP BY #tblDynamicColumnNames.XmlFieldName 
  39.  
  40. SELECT @pivotValues = LEFT(@pivotValues, LEN(@pivotValues) - 1)     
  41.  
  42. DECLARE @sqlString nvarchar(1000)
  43. SELECT @sqlString = 'select * from #tblDynamicColumnValues PIVOT (MAX(StringValue) FOR XmlFieldName IN (' + @pivotValues + ')) AS InfoPivot for xml raw(''Address''), ROOT(''Addresses''), TYPE'
  44. EXEC (@sqlString)
  45.  
  46. drop table #tblDynamicColumnValues
  47. drop table #tblDynamicColumnNames
  48.  
The output that gets produced is (notice how is uses whatever I have in the DB for the XmlFieldName for each attribute)

Expand|Select|Wrap|Line Numbers
  1. <Addresses>
  2.   <Address EntityID="22619" City="Conroe" Country="" CountryCode="" State="TX" StreetAddress1="3205 W. Davis, Sp Ed Dept" StreetAddress2="Special Education Dept." StreetAddress3="" ZipCode="77304" />
  3.   <Address EntityID="22620" City="Kermit" Country="" CountryCode="" State="TX" StreetAddress1="601 South Poplar" StreetAddress2="" StreetAddress3="" ZipCode="79745" />
  4.   <Address EntityID="22621" City="Pleasanton" Country="" CountryCode="" State="TX" StreetAddress1="831 Stadium Drive" StreetAddress2="" StreetAddress3="" ZipCode="78064" />
  5.   <Address EntityID="22622" StreetAddress1="920 Burke" StreetAddress2="" />
  6. </Addresses>
  7.  
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



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]
Apr 10 '07 #16
iburyak
1,017 Expert 512MB
Try this:

[PHP]DECLARE @myXML xml

exec <sproc> <someid>, @myXML output

select @myXML[/PHP]
Apr 10 '07 #17
iburyak
1,017 Expert 512MB
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].[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)


--- 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 #tblDynamicColumnValues
drop table #tblDynamicColumnNames

[/PHP]
Apr 10 '07 #18
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.

Expand|Select|Wrap|Line Numbers
  1. DECLARE @xmlResults table
  2. (
  3.     resultsXml xml
  4. )
  5.  
  6. insert @xmlResults
  7.     exec [GenerateAddressesForExports] 2499
  8.  
  9. select * from @xmlResults
  10.  

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

Brian



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

See my comments below:

[PHP]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)


--- 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 #tblDynamicColumnValues
drop table #tblDynamicColumnNames

[/PHP]
Apr 10 '07 #19
iburyak
1,017 Expert 512MB
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.

Expand|Select|Wrap|Line Numbers
  1. ALTER PROCEDURE [dbo].[GenerateAddressesForExports]
  2. (
  3.     @AssociationID bigint,
  4.         @myXML xml output     
  5.  
  6. AS
  7.  
  8. CREATE TABLE #tblDynamicColumnNames 
  9.    XmlFieldName nvarchar(50) NOT NULL PRIMARY KEY 
  10.  
  11. INSERT INTO #tblDynamicColumnNames 
  12.     select XmlFieldName from DefaultAddressFieldDefs
  13.     union all
  14.     select XmlFieldName from CustomAddressFieldDefs ca
  15.         where ca.AssociationId = @AssociationID
  16.  
  17. CREATE TABLE #tblDynamicColumnValues 
  18. (    
  19.    EntityID bigint NOT NULL,
  20.    XmlFieldName nvarchar(50) NOT NULL,
  21.    StringValue nvarchar(500) NOT NULL 
  22.  
  23. INSERT INTO #tblDynamicColumnValues 
  24.     select top 50         
  25.         a.EntityId,
  26.         daf.XmlFieldName,
  27.         af.StringValue
  28.     from defaultaddressfielddefs daf
  29.     inner join addressfields af on af.AddressFieldDefId = daf.AddressFieldDefId
  30.     inner join addresses a on a.AddressId = af.Addressid
  31.     inner join Membership m on m.EntityId = a.EntityId    
  32.     where m.AssociationID = @AssociationId
  33.  
  34. DECLARE @pivotValues nvarchar(1000)
  35. SELECT @pivotValues = ''
  36.  
  37. SELECT @pivotValues = @pivotValues + '[' + #tblDynamicColumnNames.XmlFieldName + '],' 
  38. FROM #tblDynamicColumnNames
  39. GROUP BY #tblDynamicColumnNames.XmlFieldName 
  40.  
  41. SELECT @pivotValues = LEFT(@pivotValues, LEN(@pivotValues) - 1)     
  42.  
  43. DECLARE @sqlString nvarchar(1000)
  44. SELECT @sqlString = 'select * from #tblDynamicColumnValues PIVOT (MAX(StringValue) FOR XmlFieldName IN (' + @pivotValues + ')) AS InfoPivot for xml raw(''Address''), ROOT(''Addresses''), TYPE'
  45. --EXEC (@sqlString)
  46.  
  47. exec sp_executesql @sqlString, N'@RetXML XML output', @RetXML = @myXML output
  48.  
  49.  
  50. drop table #tblDynamicColumnValues
  51. drop table #tblDynamicColumnNames 
Apr 10 '07 #20
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.

Expand|Select|Wrap|Line Numbers
  1. DECLARE @xmlResults table
  2. (
  3.     resultsXml xml
  4. )
  5.  
  6. insert @xmlResults
  7.     exec [GenerateAddressesForExports] 2499
  8.  

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

The first part gives me back something like

EntityID Attribute
1 Street="Whatever"
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 :)

Expand|Select|Wrap|Line Numbers
  1. DECLARE @fuckThisShit TABLE
  2. (
  3.     EntityId bigint,
  4.     Attribute nvarchar(max)    
  5.  
  6. DECLARE @resultsTable TABLE
  7. (
  8.     EntityId bigint,
  9.     Attributes nvarchar(max)    
  10.  
  11. DECLARE @IsFirst bit
  12. DECLARE @EntityId bigint
  13. DECLARE @CurrentEntityId bigint
  14. DECLARE @Attribute nvarchar(max)
  15. DECLARE @attributes nvarchar(max)
  16.  
  17. -- grab all our data!
  18. insert into @fuckThisShit
  19.     SELECT a.EntityId, cast ( daf.XmlFieldName + '="' + af.StringValue + '" ' as nvarchar(max))
  20.     from defaultaddressfielddefs daf
  21.         inner join addressfields af on af.AddressFieldDefId = daf.AddressFieldDefId
  22.         inner join addresses a on a.AddressId = af.AddressId
  23.         inner join membership m on a.EntityId = m.EntityId
  24.     where af.StringValue <> ''    
  25.  
  26. --select * from @fuckThisShit 
  27.  
  28. -- begin cursor crap!
  29.  
  30. SET @CurrentEntityId = -1
  31. SET @attributes = ''
  32.  
  33. DECLARE c1 CURSOR READ_ONLY FOR
  34.     select EntityId, Attribute from @fuckThisShit
  35.  
  36. OPEN c1
  37.  
  38. FETCH NEXT FROM c1 INTO @EntityId, @Attribute
  39. SET @CurrentEntityId = @EntityId
  40.  
  41. WHILE @@FETCH_STATUS = 0
  42. BEGIN        
  43.  
  44.     SET @attributes = @attributes + ' ' + @Attribute    
  45.  
  46.     FETCH NEXT FROM c1 INTO @EntityId, @Attribute    
  47.  
  48.     IF ( @EntityID > @CurrentEntityId ) 
  49.     BEGIN        
  50.         insert into @resultsTable ( EntityId, Attributes )    VALUES ( @CurrentEntityId, @attributes )        
  51.         SET @attributes = ''                                    
  52.         SET @CurrentEntityId = @EntityID                
  53.     END    
  54. END
  55.  
  56. insert into @resultsTable ( EntityId, Attributes )    VALUES ( @CurrentEntityId, @attributes )    
  57.  
  58. CLOSE c1
  59. DEALLOCATE c1
  60.  
  61.  
  62. select * from @resultsTable 
  63.  
  64.  




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.

Expand|Select|Wrap|Line Numbers
  1. ALTER PROCEDURE [dbo].[GenerateAddressesForExports]
  2. (
  3.     @AssociationID bigint,
  4.         @myXML xml output     
  5.  
  6. AS
  7.  
  8. CREATE TABLE #tblDynamicColumnNames 
  9.    XmlFieldName nvarchar(50) NOT NULL PRIMARY KEY 
  10.  
  11. INSERT INTO #tblDynamicColumnNames 
  12.     select XmlFieldName from DefaultAddressFieldDefs
  13.     union all
  14.     select XmlFieldName from CustomAddressFieldDefs ca
  15.         where ca.AssociationId = @AssociationID
  16.  
  17. CREATE TABLE #tblDynamicColumnValues 
  18. (    
  19.    EntityID bigint NOT NULL,
  20.    XmlFieldName nvarchar(50) NOT NULL,
  21.    StringValue nvarchar(500) NOT NULL 
  22.  
  23. INSERT INTO #tblDynamicColumnValues 
  24.     select top 50         
  25.         a.EntityId,
  26.         daf.XmlFieldName,
  27.         af.StringValue
  28.     from defaultaddressfielddefs daf
  29.     inner join addressfields af on af.AddressFieldDefId = daf.AddressFieldDefId
  30.     inner join addresses a on a.AddressId = af.Addressid
  31.     inner join Membership m on m.EntityId = a.EntityId    
  32.     where m.AssociationID = @AssociationId
  33.  
  34. DECLARE @pivotValues nvarchar(1000)
  35. SELECT @pivotValues = ''
  36.  
  37. SELECT @pivotValues = @pivotValues + '[' + #tblDynamicColumnNames.XmlFieldName + '],' 
  38. FROM #tblDynamicColumnNames
  39. GROUP BY #tblDynamicColumnNames.XmlFieldName 
  40.  
  41. SELECT @pivotValues = LEFT(@pivotValues, LEN(@pivotValues) - 1)     
  42.  
  43. DECLARE @sqlString nvarchar(1000)
  44. SELECT @sqlString = 'select * from #tblDynamicColumnValues PIVOT (MAX(StringValue) FOR XmlFieldName IN (' + @pivotValues + ')) AS InfoPivot for xml raw(''Address''), ROOT(''Addresses''), TYPE'
  45. --EXEC (@sqlString)
  46.  
  47. exec sp_executesql @sqlString, N'@RetXML XML output', @RetXML = @myXML output
  48.  
  49.  
  50. drop table #tblDynamicColumnValues
  51. drop table #tblDynamicColumnNames 
Apr 10 '07 #21
iburyak
1,017 Expert 512MB
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 #tblDynamicColumnValues PIVOT (MAX(StringValue) FOR XmlFieldName IN (' + @pivotValues + ')) AS InfoPivot for xml raw(''Address''), ROOT(''Addresses''), TYPE


You have to somehow return result into this variable
Like

Select @RetXML =


See working example that I created:
1. Create test procedure:
Expand|Select|Wrap|Line Numbers
  1. Create proc TestXML
  2. @myXML varchar(1000) output
  3. AS
  4. DECLARE @sqlString nvarchar(1000)
  5. SELECT @sqlString = 'select @RetXML = ''<Root><Address attr=Value12/></Root>'''
  6.  
  7. exec sp_executesql @sqlString, N'@RetXML varchar(1000) output',  @RetXML=@myXML output
2. Execute procedure:

Expand|Select|Wrap|Line Numbers
  1. Declare @myXML varchar(1000)
  2. exec TestXML @myXML output
  3. Select @myXML
Good Luck.
I feel your pain.... :)
Apr 10 '07 #22
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 :)


Expand|Select|Wrap|Line Numbers
  1. create function UDF_GetAddressAsXML (@AddressID int)
  2. returns nvarchar(max)
  3. as
  4. begin
  5.  
  6.     DECLARE @xml NVARCHAR(MAX)
  7.     SET @xml = '<Address '
  8.  
  9.     SELECT @xml = @xml + 'AddressType="' + cat.Description + '" '
  10.     FROM CustomAddressTypes cat inner join 
  11.     Addresses a on a.AddressTypeId = cat.AddressTypeId
  12.     where a.AddressId = @AddressId
  13.  
  14.     SELECT @xml = @xml + REPLACE(XmlFieldName, ' ', '') + '="' + StringValue + '" ' 
  15.     FROM AddressFields af
  16.     INNER JOIN DefaultAddressFieldDefs dafd ON af.AddressFieldDefID=dafd.AddressFieldDefID
  17.     where AddressId = @AddressId
  18.  
  19.     SET @xml = @xml + ' />'
  20.     return @xml 
  21.  
  22. end
  23.  
  24.  
  25. GO
  26.  
  27. create function UDF_GetEntityAddressesAsXML (@EntityID int)
  28. returns xml
  29. as
  30. begin
  31.  
  32.     declare @xml nvarchar(max)
  33.  
  34.     set @xml = '<Addresses>' 
  35.     select @xml = @xml + dbo.UDF_GetAddressAsXML(a.AddressId) from
  36.         Addresses a where a.EntityId = @EntityID
  37.     set @xml = @xml + '</Addresses>'
  38.  
  39.     return CAST(@xml as xml) 
  40. end
  41.  
  42. GO
  43.  
  44. SELECT dbo.UDF_GetEntityAddressesAsXML(13)
  45.  
  46. GO
Apr 11 '07 #23

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Koen | last post by:
Hi all, At work I created a database which is really helpful. The database is used by approx 15 users. Everything worked great, until I added some 'scoreboard' forms and reports. I get the...
1
by: Stefan V. | last post by:
Hello! I am trying to convert a query written for SQL Server 2000 database tables, to a MS Access query. Here is what I have in SQL Server: SELECT t2.*, CASE WHEN t2.QType = '3' THEN...
3
by: pw | last post by:
Hi, I am having a mental block trying to figure out how to code this. Two tables: "tblQuestions" (fields = quesnum, questype, question) "tblAnswers" (fields = clientnum, quesnum, questype,...
1
by: Abhi | last post by:
Hi! I have the following table: id, questions, answers 1,q1,1 2,q1,5 1,q3,2 3,q1,3 4,q1,5
4
by: Bob Alston | last post by:
Some more, rather specific Access performance questions. IN a split front-end & back-end Access/Jet ONLY LAN situation, and with all query criteria fields and join fields indexed: 1. Is is...
7
by: serge | last post by:
How can I run a single SP by asking multiple sales question either by using the logical operator AND for all the questions; or using the logical operator OR for all the questions. So it's always...
6
by: kenshiro | last post by:
Hi All, I'm having a problem with some VBA code in one of my Access 2003 databases. I'm getting the following error when running code behind a command button on a form: "Item not found in this...
12
by: si_owen | last post by:
Hi all, I have a SQL query that worked fine in my project until it came to testing. I found that the NvarChar fields I have wont accept the use of an ' My code and query is here does anyone...
16
by: Dave | last post by:
Hi I'm having trouble with some sql and don't know if my table design is incorrect. I have a table of delegate names. I have a table of possible questions the delegate can answer. I then...
6
by: pippapippa | last post by:
I should be most grateful for a little advice. I have used Access 2000 & latterly 2002. Am about to upgrade since it is evident that documentation, tutorials etc are more readily available in...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.