More XML query questions

Originally Posted by pnkfloyd

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

Originally Posted by iburyak

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.

Originally Posted by iburyak

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.

Originally Posted by pnkfloyd

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!

Originally Posted by iburyak

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.

Originally Posted by iburyak

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.

Originally Posted by iburyak

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.

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]

Originally Posted by iburyak

Try this:

[PHP]DECLARE @myXML xml

exec <sproc> <someid>, @myXML output

select @myXML[/PHP]

Originally Posted by iburyak

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]

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.