-
1. Re: Column value as identifier in XMLELEMENTmethod
shawkins May 30, 2012 11:51 AM (in response to rajkota)Since it's in the spec most SQL/XML implementations require the element name to be an identifier. To be more dynamic, you could use explicit element construction via XMLQUERY:
SELECT A.c2, XMLQUERY('element {$n} {attribute SYSID {$attr}}' passing A.c1 AS n, convert(A.c3, string) AS attr) FROM tab AS A
Steve
-
2. Re: Column value as identifier in XMLELEMENTmethod
rajkota May 31, 2012 6:24 AM (in response to shawkins)Thanks Steve. Your reply has helped me to continue my research in my work.
Raj
-
3. Re: Column value as identifier in XMLELEMENTmethod
rajkota Jun 1, 2012 4:19 PM (in response to rajkota)Hi Steve,
If I run the below query:
SELECT xmlserialize(XMLQUERY('element {$n} {attribute SYSID {$attr}, attribute NID{$attr1}}' passing A.c1 AS n, convert(A.c3, string) AS attr,convert(A.c4, string) AS attr1)) FROM tab AS A
I get the response as:
expr
---------------------------------------------------------
<ELEMENT1 SYSID="SYSID1 " NID="NID1"/>
but I am expecting response like <ELEMENT1 SYSID="SYSID1 " NID="NID1"></ELEMENT1>
Could you please let me know how can I achieve it?
Thanks,
Raj
-
4. Re: Column value as identifier in XMLELEMENTmethod
rajkota Jun 3, 2012 8:27 AM (in response to rajkota)As an alternative, String scalar function 'replace()' is helping me to get the desired result.
Now if I run the same query with replace () method:
SELECT replace(xmlserialize(XMLQUERY('element {$n} {attribute SYSID {$attr}, attribute NID{$attr1}}' passing A.c1 AS n, convert(A.c3, string) AS attr,convert(A.c4, string) AS attr) as string), '/>', concat(concat('></', A.c1), '>') ) FROM tab AS A
I get the response like:
expr
---------------------------------------------------------
<ELEMENT1 SYSID="SYSID1 " NID="NID1"></ELEMENT>
Thanks,
Raj
-
5. Re: Column value as identifier in XMLELEMENTmethod
shawkins Jun 4, 2012 4:58 AM (in response to rajkota)Raj,
The forms are equivalent and I'm not aware of any output property that would put the full element form rather than the short-hand. The replace workaround should be fine or if you can add whitespace to the element content, then the end tag will automatically be added:
XMLQUERY('element {$n} {attribute SYSID {$attr}, " "}' passing ...
Steve
-
6. Re: Column value as identifier in XMLELEMENTmethod
rajkota Jun 4, 2012 5:36 AM (in response to shawkins)Hi Steve,
Thank you. I tried this way, But I have to compare the result with another xml string, which does not have space between starting and ending tags. So I have no option other than replace () for my requirement and which is working quite decently.Thanks,
Raj