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 -
DECLARE @myXML xml
-
SET @myXML = ''
-
-
SELECT top 1 daf.XmlFieldName, af.StringValue, @myXML.query('
-
<Address
-
"{sql:column("daf.XmlFieldName")}"="{sql:column("af.StringValue")}"
-
/>')
-
AS Result
-
from defaultaddressfielddefs daf
-
inner join addressfields af on af.AddressFieldDefId = daf.AddressFieldDefId
-
where af.StringValue <> ''
-
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 -
DECLARE @myXML xml
-
SET @myXML = ''
-
-
SELECT top 1 daf.XmlFieldName, af.StringValue, @myXML.query('
-
<Address
-
"{sql:column("daf.XmlFieldName")}"="{sql:column("af.StringValue")}"
-
/>')
-
AS Result
-
from defaultaddressfielddefs daf
-
inner join addressfields af on af.AddressFieldDefId = daf.AddressFieldDefId
-
where af.StringValue <> ''
-
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.
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.
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.
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.
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!
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.
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.
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.
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.
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.
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
I understand what you mean but I don't know how to make ti work.
Sorry.
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]
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. -
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)
- SELECT @sqlString = 'select * from #tblDynamicColumnValues PIVOT (MAX(StringValue) FOR XmlFieldName IN (' + @pivotValues + ')) AS InfoPivot for xml raw(''Address''), ROOT(''Addresses''), TYPE'
-
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) -
<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
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]
Try this:
[PHP]DECLARE @myXML xml
exec <sproc> <someid>, @myXML output
select @myXML[/PHP]
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]
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. -
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
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]
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. - 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
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. -
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="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 :) -
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
-
-
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. - 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
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: -
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: - Declare @myXML varchar(1000)
-
exec TestXML @myXML output
-
Select @myXML
Good Luck.
I feel your pain.... :)
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 :) - 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
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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,...
|
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
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
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
|
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...
| |