More XML query questions

[pnkfloyd]

Can someone explain why this won't work? It works if I take out the first "{sql:column("d af.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 <> ''

[pnkfloyd]

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("d af.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 <> ''

[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.

[pnkfloyd]

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="unbo unded">
<xs:element name="Address">
<xs:complexType >
<xs:attribute name="AddressTy pe" type="xs:string " use="required" />
<xs:attribute name="City" type="xs:string " />
<xs:attribute name="Country" type="xs:string " />
<xs:attribute name="CountryCo de" type="xs:string " />
<xs:attribute name="State" type="xs:string " />
<xs:attribute name="StreetAdd ress1" type="xs:string " />
<xs:attribute name="StreetAdd ress2" type="xs:string " />
<xs:attribute name="StreetAdd ress3" 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.

[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.

[pnkfloyd]

Yes, close. I am looking actually for this

<Addresses>
<Address AddressType="<s ome 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.

[pnkfloyd]

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="<s ome 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!

[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.

[pnkfloyd]

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.

[pnkfloyd]

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="unbo unded">
- <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.

[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!Addre ssType],
null [Address!2!City],
null [Address!2!Count ry],
null [Address!2!Count ryCode],
null [Address!2!State],
null [Address!2!Stree tAddress1],
null [Address!2!Stree tAddress2],
null [Address!2!Stree tAddress3],
null [Address!2!ZipCo de]
UNION ALL
select 2 TAG,
1 Parent,
null,
max(case when ColumnName = 'AddressType' then Value else '' end) [Address!2!Addre ssType],
max(case when ColumnName = 'City' then Value else '' end) [Address!2!City],
max(case when ColumnName = 'Country' then Value else '' end) [Address!2!Count ry],
max(case when ColumnName = 'CountryCode' then Value else '' end) [Address!2!Count ryCode],
max(case when ColumnName = 'State' then Value else '' end) [Address!2!State],
max(case when ColumnName = 'StreetAddress1 ' then Value else '' end) [Address!2!Stree tAddress1],
max(case when ColumnName = 'StreetAddress2 ' then Value else '' end) [Address!2!Stree tAddress2],
max(case when ColumnName = 'StreetAddress3 ' then Value else '' end) [Address!2!Stree tAddress3],
max(case when ColumnName = 'ZipCode' then Value else '' end) [Address!2!ZipCo de]
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.

[pnkfloyd]

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!Addre ssType],
null [Address!2!City],
null [Address!2!Count ry],
null [Address!2!Count ryCode],
null [Address!2!State],
null [Address!2!Stree tAddress1],
null [Address!2!Stree tAddress2],
null [Address!2!Stree tAddress3],
null [Address!2!ZipCo de]
UNION ALL
select 2 TAG,
1 Parent,
null,
max(case when ColumnName = 'AddressType' then Value else '' end) [Address!2!Addre ssType],
max(case when ColumnName = 'City' then Value else '' end) [Address!2!City],
max(case when ColumnName = 'Country' then Value else '' end) [Address!2!Count ry],
max(case when ColumnName = 'CountryCode' then Value else '' end) [Address!2!Count ryCode],
max(case when ColumnName = 'State' then Value else '' end) [Address!2!State],
max(case when ColumnName = 'StreetAddress1 ' then Value else '' end) [Address!2!Stree tAddress1],
max(case when ColumnName = 'StreetAddress2 ' then Value else '' end) [Address!2!Stree tAddress2],
max(case when ColumnName = 'StreetAddress3 ' then Value else '' end) [Address!2!Stree tAddress3],
max(case when ColumnName = 'ZipCode' then Value else '' end) [Address!2!ZipCo de]
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.

[pnkfloyd]

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

[iburyak]

I understand what you mean but I don't know how to make ti work.


Sorry.

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