I have a program which generate a xml file from dataset using
DataSet.WriteXML function. The result is as follow:
<DataSet xmlns="http://tempuri.org/DataSet1.xsd">
<TB TB_NO="NN01001" DESCRIPTION="NN " DATE="1/16/2007
12:00:00 AM" Name=" "
E_MAIL="test@.test.com">
<TD TD_NO="NN1-1 " LINE_NAME="FREE TRADE ZONE
" ADD1=" " TRSS="3" TYPEDESC="
" >
<DESCRIPTION SEQ_NO="001 " MARKS="RESOURCES " DSCP="
" />
<DESCRIPTION SEQ_NO="002 " MARKS=" " DSCP="
" />
<DESCRIPTION SEQ_NO="003 " MARKS="P.O.:11111111 " DSCP="
" />
<DESCRIPTION SEQ_NO="004 " MARKS="MADE IN CHINA " DSCP="
" />
<DESCRIPTION SEQ_NO="005 " MARKS=" " DSCP="
" />
<DESCRIPTION SEQ_NO="006 " MARKS=" " DSCP="
" />
</TD>
</TB>
</DataSet>
Is there any setting to set it to generate xml file with trim space values
and result should output as :
<DataSet xmlns="http://tempuri.org/DataSet1.xsd">
<TB TB_NO="NN01001" DESCRIPTION="NN" DATE="1/16/2007 12:00:00 AM" Name=""
E_MAIL="test@.test.com">
<TD TD_NO="NN1-1" LINE_NAME="FREE TRADE ZONE" ADD1="" TRSS="3"
TYPEDESC="" >
<DESCRIPTION SEQ_NO="001" MARKS="RESOURCES" DSCP="" />
<DESCRIPTION SEQ_NO="002" MARKS="" DSCP="" />
<DESCRIPTION SEQ_NO="003" MARKS="P.O.:11111111" DSCP="" />
<DESCRIPTION SEQ_NO="004" MARKS="MADE IN CHINA" DSCP="" />
<DESCRIPTION SEQ_NO="005" MARKS="" DSCP="" />
<DESCRIPTION SEQ_NO="006" MARKS="" DSCP="" />
</TD>
</TB>
</DataSet>
In your SQL SELECT or stored procedure just use LTRIM(column_name) or
RTRIM(column_name) to remove unwanted spaces then the resulting dataset will
not have them for the WriteXML format.
So to correct your data below it would be something like:
SELECT
RTRIM(TB_NO),
RTRIM(DESCRIPTION)
...
"ABC" <abc@.abc.com> wrote in message
news:elMHZEiOHHA.4484@.TK2MSFTNGP02.phx.gbl...
>I have a program which generate a xml file from dataset using
> DataSet.WriteXML function. The result is as follow:
> <DataSet xmlns="http://tempuri.org/DataSet1.xsd">
> <TB TB_NO="NN01001" DESCRIPTION="NN " DATE="1/16/2007
> 12:00:00 AM" Name=" "
> E_MAIL="test@.test.com">
> <TD TD_NO="NN1-1 " LINE_NAME="FREE TRADE ZONE
> " ADD1=" " TRSS="3" TYPEDESC="
> " >
> <DESCRIPTION SEQ_NO="001 " MARKS="RESOURCES " DSCP="
> " />
> <DESCRIPTION SEQ_NO="002 " MARKS=" " DSCP="
> " />
> <DESCRIPTION SEQ_NO="003 " MARKS="P.O.:11111111 " DSCP="
> " />
> <DESCRIPTION SEQ_NO="004 " MARKS="MADE IN CHINA " DSCP="
> " />
> <DESCRIPTION SEQ_NO="005 " MARKS=" " DSCP="
> " />
> <DESCRIPTION SEQ_NO="006 " MARKS=" " DSCP="
> " />
> </TD>
> </TB>
> </DataSet>
>
> Is there any setting to set it to generate xml file with trim space values
> and result should output as :
> <DataSet xmlns="http://tempuri.org/DataSet1.xsd">
> <TB TB_NO="NN01001" DESCRIPTION="NN" DATE="1/16/2007 12:00:00 AM" Name=""
> E_MAIL="test@.test.com">
> <TD TD_NO="NN1-1" LINE_NAME="FREE TRADE ZONE" ADD1="" TRSS="3"
> TYPEDESC="" >
> <DESCRIPTION SEQ_NO="001" MARKS="RESOURCES" DSCP="" />
> <DESCRIPTION SEQ_NO="002" MARKS="" DSCP="" />
> <DESCRIPTION SEQ_NO="003" MARKS="P.O.:11111111" DSCP="" />
> <DESCRIPTION SEQ_NO="004" MARKS="MADE IN CHINA" DSCP="" />
> <DESCRIPTION SEQ_NO="005" MARKS="" DSCP="" />
> <DESCRIPTION SEQ_NO="006" MARKS="" DSCP="" />
> </TD>
> </TB>
> </DataSet>
>
>
sql
Showing posts with label xml. Show all posts
Showing posts with label xml. Show all posts
Friday, March 30, 2012
How to trim XML's attribute values?
I have a program which generate a xml file from dataset using
DataSet.WriteXML function. The result is as follow:
<DataSet xmlns="http://tempuri.org/DataSet1.xsd">
<TB TB_NO="NN01001" DESCRIPTION="NN " DATE="1/16/2007
12:00:00 AM" Name=" "
E_MAIL="test@.test.com">
<TD TD_NO="NN1-1 " LINE_NAME="FREE TRADE ZONE
" ADD1=" " TRSS="3" TYPEDESC="
" >
<DESCRIPTION SEQ_NO="001 " MARKS="RESOURCES " DSCP="
" />
<DESCRIPTION SEQ_NO="002 " MARKS=" " DSCP="
" />
<DESCRIPTION SEQ_NO="003 " MARKS="P.O.:11111111 " DSCP="
" />
<DESCRIPTION SEQ_NO="004 " MARKS="MADE IN CHINA " DSCP="
" />
<DESCRIPTION SEQ_NO="005 " MARKS=" " DSCP="
" />
<DESCRIPTION SEQ_NO="006 " MARKS=" " DSCP="
" />
</TD>
</TB>
</DataSet>
Is there any setting to set it to generate xml file with trim space values
and result should output as :
<DataSet xmlns="http://tempuri.org/DataSet1.xsd">
<TB TB_NO="NN01001" DESCRIPTION="NN" DATE="1/16/2007 12:00:00 AM" Name=""
E_MAIL="test@.test.com">
<TD TD_NO="NN1-1" LINE_NAME="FREE TRADE ZONE" ADD1="" TRSS="3"
TYPEDESC="" >
<DESCRIPTION SEQ_NO="001" MARKS="RESOURCES" DSCP="" />
<DESCRIPTION SEQ_NO="002" MARKS="" DSCP="" />
<DESCRIPTION SEQ_NO="003" MARKS="P.O.:11111111" DSCP="" />
<DESCRIPTION SEQ_NO="004" MARKS="MADE IN CHINA" DSCP="" />
<DESCRIPTION SEQ_NO="005" MARKS="" DSCP="" />
<DESCRIPTION SEQ_NO="006" MARKS="" DSCP="" />
</TD>
</TB>
</DataSet>In your SQL SELECT or stored procedure just use LTRIM(column_name) or
RTRIM(column_name) to remove unwanted spaces then the resulting dataset will
not have them for the WriteXML format.
So to correct your data below it would be something like:
SELECT
RTRIM(TB_NO),
RTRIM(DESCRIPTION)
...
"ABC" <abc@.abc.com> wrote in message
news:elMHZEiOHHA.4484@.TK2MSFTNGP02.phx.gbl...
>I have a program which generate a xml file from dataset using
> DataSet.WriteXML function. The result is as follow:
> <DataSet xmlns="http://tempuri.org/DataSet1.xsd">
> <TB TB_NO="NN01001" DESCRIPTION="NN " DATE="1/16/2007
> 12:00:00 AM" Name=" "
> E_MAIL="test@.test.com">
> <TD TD_NO="NN1-1 " LINE_NAME="FREE TRADE ZONE
> " ADD1=" " TRSS="3" TYPEDESC="
> " >
> <DESCRIPTION SEQ_NO="001 " MARKS="RESOURCES " DSCP="
> " />
> <DESCRIPTION SEQ_NO="002 " MARKS=" " DSCP="
> " />
> <DESCRIPTION SEQ_NO="003 " MARKS="P.O.:11111111 " DSCP="
> " />
> <DESCRIPTION SEQ_NO="004 " MARKS="MADE IN CHINA " DSCP="
> " />
> <DESCRIPTION SEQ_NO="005 " MARKS=" " DSCP="
> " />
> <DESCRIPTION SEQ_NO="006 " MARKS=" " DSCP="
> " />
> </TD>
> </TB>
> </DataSet>
>
> Is there any setting to set it to generate xml file with trim space values
> and result should output as :
> <DataSet xmlns="http://tempuri.org/DataSet1.xsd">
> <TB TB_NO="NN01001" DESCRIPTION="NN" DATE="1/16/2007 12:00:00 AM" Name=""
> E_MAIL="test@.test.com">
> <TD TD_NO="NN1-1" LINE_NAME="FREE TRADE ZONE" ADD1="" TRSS="3"
> TYPEDESC="" >
> <DESCRIPTION SEQ_NO="001" MARKS="RESOURCES" DSCP="" />
> <DESCRIPTION SEQ_NO="002" MARKS="" DSCP="" />
> <DESCRIPTION SEQ_NO="003" MARKS="P.O.:11111111" DSCP="" />
> <DESCRIPTION SEQ_NO="004" MARKS="MADE IN CHINA" DSCP="" />
> <DESCRIPTION SEQ_NO="005" MARKS="" DSCP="" />
> <DESCRIPTION SEQ_NO="006" MARKS="" DSCP="" />
> </TD>
> </TB>
> </DataSet>
>
>
DataSet.WriteXML function. The result is as follow:
<DataSet xmlns="http://tempuri.org/DataSet1.xsd">
<TB TB_NO="NN01001" DESCRIPTION="NN " DATE="1/16/2007
12:00:00 AM" Name=" "
E_MAIL="test@.test.com">
<TD TD_NO="NN1-1 " LINE_NAME="FREE TRADE ZONE
" ADD1=" " TRSS="3" TYPEDESC="
" >
<DESCRIPTION SEQ_NO="001 " MARKS="RESOURCES " DSCP="
" />
<DESCRIPTION SEQ_NO="002 " MARKS=" " DSCP="
" />
<DESCRIPTION SEQ_NO="003 " MARKS="P.O.:11111111 " DSCP="
" />
<DESCRIPTION SEQ_NO="004 " MARKS="MADE IN CHINA " DSCP="
" />
<DESCRIPTION SEQ_NO="005 " MARKS=" " DSCP="
" />
<DESCRIPTION SEQ_NO="006 " MARKS=" " DSCP="
" />
</TD>
</TB>
</DataSet>
Is there any setting to set it to generate xml file with trim space values
and result should output as :
<DataSet xmlns="http://tempuri.org/DataSet1.xsd">
<TB TB_NO="NN01001" DESCRIPTION="NN" DATE="1/16/2007 12:00:00 AM" Name=""
E_MAIL="test@.test.com">
<TD TD_NO="NN1-1" LINE_NAME="FREE TRADE ZONE" ADD1="" TRSS="3"
TYPEDESC="" >
<DESCRIPTION SEQ_NO="001" MARKS="RESOURCES" DSCP="" />
<DESCRIPTION SEQ_NO="002" MARKS="" DSCP="" />
<DESCRIPTION SEQ_NO="003" MARKS="P.O.:11111111" DSCP="" />
<DESCRIPTION SEQ_NO="004" MARKS="MADE IN CHINA" DSCP="" />
<DESCRIPTION SEQ_NO="005" MARKS="" DSCP="" />
<DESCRIPTION SEQ_NO="006" MARKS="" DSCP="" />
</TD>
</TB>
</DataSet>In your SQL SELECT or stored procedure just use LTRIM(column_name) or
RTRIM(column_name) to remove unwanted spaces then the resulting dataset will
not have them for the WriteXML format.
So to correct your data below it would be something like:
SELECT
RTRIM(TB_NO),
RTRIM(DESCRIPTION)
...
"ABC" <abc@.abc.com> wrote in message
news:elMHZEiOHHA.4484@.TK2MSFTNGP02.phx.gbl...
>I have a program which generate a xml file from dataset using
> DataSet.WriteXML function. The result is as follow:
> <DataSet xmlns="http://tempuri.org/DataSet1.xsd">
> <TB TB_NO="NN01001" DESCRIPTION="NN " DATE="1/16/2007
> 12:00:00 AM" Name=" "
> E_MAIL="test@.test.com">
> <TD TD_NO="NN1-1 " LINE_NAME="FREE TRADE ZONE
> " ADD1=" " TRSS="3" TYPEDESC="
> " >
> <DESCRIPTION SEQ_NO="001 " MARKS="RESOURCES " DSCP="
> " />
> <DESCRIPTION SEQ_NO="002 " MARKS=" " DSCP="
> " />
> <DESCRIPTION SEQ_NO="003 " MARKS="P.O.:11111111 " DSCP="
> " />
> <DESCRIPTION SEQ_NO="004 " MARKS="MADE IN CHINA " DSCP="
> " />
> <DESCRIPTION SEQ_NO="005 " MARKS=" " DSCP="
> " />
> <DESCRIPTION SEQ_NO="006 " MARKS=" " DSCP="
> " />
> </TD>
> </TB>
> </DataSet>
>
> Is there any setting to set it to generate xml file with trim space values
> and result should output as :
> <DataSet xmlns="http://tempuri.org/DataSet1.xsd">
> <TB TB_NO="NN01001" DESCRIPTION="NN" DATE="1/16/2007 12:00:00 AM" Name=""
> E_MAIL="test@.test.com">
> <TD TD_NO="NN1-1" LINE_NAME="FREE TRADE ZONE" ADD1="" TRSS="3"
> TYPEDESC="" >
> <DESCRIPTION SEQ_NO="001" MARKS="RESOURCES" DSCP="" />
> <DESCRIPTION SEQ_NO="002" MARKS="" DSCP="" />
> <DESCRIPTION SEQ_NO="003" MARKS="P.O.:11111111" DSCP="" />
> <DESCRIPTION SEQ_NO="004" MARKS="MADE IN CHINA" DSCP="" />
> <DESCRIPTION SEQ_NO="005" MARKS="" DSCP="" />
> <DESCRIPTION SEQ_NO="006" MARKS="" DSCP="" />
> </TD>
> </TB>
> </DataSet>
>
>
Monday, March 12, 2012
How to Synchroize XML file with a tabe in SQL Server 2005?
Is there any inbuilt feature of SQL Server 2005 that will allow automatic synchronization of data in a XML file and its corresponding table in database, i.e. the data in XML file is pushed to table?
Have you considered using Scheduled Job? I mean you can import data from XML to database using Import/Export Wizard, with saving the process as a SSIS package. Then go to SQL Agent and create a job which executes the SSIS package, and you can schedule the job
how to sync PDA data (in XML format)with remote database (SQL)
hello everyone,
i am developing a application using C#and .NEt compact framework.
for that i need to sync pda data (in xml format) with the remote database server(SQL) and vice versa.
please help me out
thanks in advance.I think using CE version of windows & SQL you can workaround this issue. I suggest you to refer to http://www.microsoft.com/sql (SQL homepage) website for more details about CE products.
HTH|||satya i am using pocket pc 2002/2003 not win ce and on pocket pc i am using xml as a database and on my central server i am using sql serve.
and thanks for the reply. please guide me in this direction.
regards|||May try this SDK download (http://www.microsoft.com/windowsmobile/information/devprograms/default.mspx?gssnb=1) site for any reference.
Have you gone thru MSSQL homepage earlier?|||no i havent tried mssql before that. should i go through with or not.
one more problem how can i embed the IE (internet explorer) in my application so that from within my application i would be able to see reports generated by the server.
please|||I think you would be better of to go thru the referred websites to get more information.
I don't have exp. with other tools so cannot offer any discussion.:(
i am developing a application using C#and .NEt compact framework.
for that i need to sync pda data (in xml format) with the remote database server(SQL) and vice versa.
please help me out
thanks in advance.I think using CE version of windows & SQL you can workaround this issue. I suggest you to refer to http://www.microsoft.com/sql (SQL homepage) website for more details about CE products.
HTH|||satya i am using pocket pc 2002/2003 not win ce and on pocket pc i am using xml as a database and on my central server i am using sql serve.
and thanks for the reply. please guide me in this direction.
regards|||May try this SDK download (http://www.microsoft.com/windowsmobile/information/devprograms/default.mspx?gssnb=1) site for any reference.
Have you gone thru MSSQL homepage earlier?|||no i havent tried mssql before that. should i go through with or not.
one more problem how can i embed the IE (internet explorer) in my application so that from within my application i would be able to see reports generated by the server.
please|||I think you would be better of to go thru the referred websites to get more information.
I don't have exp. with other tools so cannot offer any discussion.:(
How to suppress namespace attributes in nested XML?
Hello,
I've got a query that dynamically generates an XML schema based on a
lookup table. Everything works fine, except that I would like to
suppress the generation of the namespace declarations on the individual
elements in xml type created by the sub-select.
Here's the code:
WITH XMLNAMESPACES ('http://tempuri.org/techFitCategories.xsd' AS tns,
DEFAULT
'http://www.w3.org/2001/XMLSchema')
SELECT 'techFitCategories' AS "@.id",
'http://tempuri.org/techFitCategories.xsd' AS
"@.targetNamespace",
'techFitCategory' AS "element/@.name",
'tns:category' AS "element/@.type",
'category' AS "simpleType/@.name",
'string' AS "simpleType/restriction/@.base",
(SELECT CategoryName AS "@.value"
FROM SkillCategories
FOR XML PATH('enumeration'), TYPE) AS
"simpleType/restriction"
FOR XML PATH('schema')
Here's the output:
<schema xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:tns="http://tempuri.org/techFitCategories.xsd"
id="techFitCategories"targetNamespace="http://tempuri.org/techFitCategories.xsd">
<element name="techFitCategory" type="tns:category" />
<simpleType name="category">
<restriction base="string">
<enumeration xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="C#" />
<enumeration xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="Java" />
<enumeration xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="Linux" />
<enumeration xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="SQL Server
2005" />
<enumeration xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="VB.NET" />
<enumeration xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="WebSphere"
/>
<enumeration xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="Windows
2003" />
</restriction>
</simpleType>
</schema>
I'd like to get rid of the namespace declarations on the
<enumeration../> elements.
Any ideas?
Thanks in advance!
ca
One way is add a prefix to the default namespace. And add the prefix to
every nodes wherever you want. This is the way many experts recommend.
Pohwan Han. Seoul. Have a nice day.
<christopher.atkins@.e-gineering.com> wrote in message
news:1140685400.006495.6120@.p10g2000cwp.googlegrou ps.com...
> Hello,
> I've got a query that dynamically generates an XML schema based on a
> lookup table. Everything works fine, except that I would like to
> suppress the generation of the namespace declarations on the individual
> elements in xml type created by the sub-select.
>
> Here's the code:
> WITH XMLNAMESPACES ('http://tempuri.org/techFitCategories.xsd' AS tns,
> DEFAULT
> 'http://www.w3.org/2001/XMLSchema')
> SELECT 'techFitCategories' AS "@.id",
> 'http://tempuri.org/techFitCategories.xsd' AS
> "@.targetNamespace",
> 'techFitCategory' AS "element/@.name",
> 'tns:category' AS "element/@.type",
> 'category' AS "simpleType/@.name",
> 'string' AS "simpleType/restriction/@.base",
> (SELECT CategoryName AS "@.value"
> FROM SkillCategories
> FOR XML PATH('enumeration'), TYPE) AS
> "simpleType/restriction"
> FOR XML PATH('schema')
>
> Here's the output:
> <schema xmlns="http://www.w3.org/2001/XMLSchema"
> xmlns:tns="http://tempuri.org/techFitCategories.xsd"
> id="techFitCategories"targetNamespace="http://tempuri.org/techFitCategories.xsd">
> <element name="techFitCategory" type="tns:category" />
> <simpleType name="category">
> <restriction base="string">
> <enumeration xmlns="http://www.w3.org/2001/XMLSchema"
> xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="C#" />
> <enumeration xmlns="http://www.w3.org/2001/XMLSchema"
> xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="Java" />
> <enumeration xmlns="http://www.w3.org/2001/XMLSchema"
> xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="Linux" />
> <enumeration xmlns="http://www.w3.org/2001/XMLSchema"
> xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="SQL Server
> 2005" />
> <enumeration xmlns="http://www.w3.org/2001/XMLSchema"
> xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="VB.NET" />
> <enumeration xmlns="http://www.w3.org/2001/XMLSchema"
> xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="WebSphere"
> />
> <enumeration xmlns="http://www.w3.org/2001/XMLSchema"
> xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="Windows
> 2003" />
> </restriction>
> </simpleType>
> </schema>
>
> I'd like to get rid of the namespace declarations on the
> <enumeration../> elements.
>
> Any ideas?
>
> Thanks in advance!
> ca
>
|||Pohwan,
Thank you kindly for your reply. If I parsed your response properly, I
don't believe this does what I'd hoped. It seems whenever you include
an XML column in a SELECT...FOR XML with the WITH NAMESPACES directive,
it will add the namespace declarations to each element of the XML.
I've tried using an xsd prefix on all the schema elements, but this
doesn't prevent it from adding the namespace attributes to the elements
in the nested XML typed column.
The only alternative I can think of is hacking a big nvarchar together:
not exactly the elegant solution I was hoping for. Don't get me wrong,
I've got a valid schema with the original query, but I'm expecting the
enumeration to get significantly large, and the result XML Schema
document would be huge--not a good thing.
If I've completely missed your point, do you think you could provide a
code example?
ca
|||You are right, Christopher. The prefix doesn't remove the namespace. It just
makes the namespace dormant. When prefixed namespaces are unused they remain
meaningless trash, while non-prefixed or default namespaces are part of
element names. Now all you need is ignoring the unused trash. Optionally you
can remove the namespace in some mid-tier such as XSLT.
Earlier I asked same question in beta SQL2005 group. About alternative of
exclude-result-prefixes of XSLT. And I got replied that there is none, IIRC,
by Michael Rys.
Pohwan Han. Seoul. Have a nice day.
"Christopher Atkins" <christopher.atkins@.e-gineering.com> wrote in message
news:1140721205.415683.28260@.u72g2000cwu.googlegro ups.com...
> Pohwan,
> Thank you kindly for your reply. If I parsed your response properly, I
> don't believe this does what I'd hoped. It seems whenever you include
> an XML column in a SELECT...FOR XML with the WITH NAMESPACES directive,
> it will add the namespace declarations to each element of the XML.
> I've tried using an xsd prefix on all the schema elements, but this
> doesn't prevent it from adding the namespace attributes to the elements
> in the nested XML typed column.
> The only alternative I can think of is hacking a big nvarchar together:
> not exactly the elegant solution I was hoping for. Don't get me wrong,
> I've got a valid schema with the original query, but I'm expecting the
> enumeration to get significantly large, and the result XML Schema
> document would be huge--not a good thing.
> If I've completely missed your point, do you think you could provide a
> code example?
> ca
>
|||We currently output all the namespaces that you provide and at every level
and there is no easy workaround. Note that the XML document is still
correct.
Best is if you file a feature request at
http://lab.msdn.microsoft.com/productfeedback/
by providing sample data, query current result and expected result.
Thanks
Michael
"Han" <hp4444@.kornet.net.korea> wrote in message
news:OP%23rjUQOGHA.3944@.tk2msftngp13.phx.gbl...
> You are right, Christopher. The prefix doesn't remove the namespace. It
> just makes the namespace dormant. When prefixed namespaces are unused they
> remain meaningless trash, while non-prefixed or default namespaces are
> part of element names. Now all you need is ignoring the unused trash.
> Optionally you can remove the namespace in some mid-tier such as XSLT.
> Earlier I asked same question in beta SQL2005 group. About alternative of
> exclude-result-prefixes of XSLT. And I got replied that there is none,
> IIRC, by Michael Rys.
> --
> Pohwan Han. Seoul. Have a nice day.
> "Christopher Atkins" <christopher.atkins@.e-gineering.com> wrote in message
> news:1140721205.415683.28260@.u72g2000cwu.googlegro ups.com...
>
I've got a query that dynamically generates an XML schema based on a
lookup table. Everything works fine, except that I would like to
suppress the generation of the namespace declarations on the individual
elements in xml type created by the sub-select.
Here's the code:
WITH XMLNAMESPACES ('http://tempuri.org/techFitCategories.xsd' AS tns,
DEFAULT
'http://www.w3.org/2001/XMLSchema')
SELECT 'techFitCategories' AS "@.id",
'http://tempuri.org/techFitCategories.xsd' AS
"@.targetNamespace",
'techFitCategory' AS "element/@.name",
'tns:category' AS "element/@.type",
'category' AS "simpleType/@.name",
'string' AS "simpleType/restriction/@.base",
(SELECT CategoryName AS "@.value"
FROM SkillCategories
FOR XML PATH('enumeration'), TYPE) AS
"simpleType/restriction"
FOR XML PATH('schema')
Here's the output:
<schema xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:tns="http://tempuri.org/techFitCategories.xsd"
id="techFitCategories"targetNamespace="http://tempuri.org/techFitCategories.xsd">
<element name="techFitCategory" type="tns:category" />
<simpleType name="category">
<restriction base="string">
<enumeration xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="C#" />
<enumeration xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="Java" />
<enumeration xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="Linux" />
<enumeration xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="SQL Server
2005" />
<enumeration xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="VB.NET" />
<enumeration xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="WebSphere"
/>
<enumeration xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="Windows
2003" />
</restriction>
</simpleType>
</schema>
I'd like to get rid of the namespace declarations on the
<enumeration../> elements.
Any ideas?
Thanks in advance!
ca
One way is add a prefix to the default namespace. And add the prefix to
every nodes wherever you want. This is the way many experts recommend.
Pohwan Han. Seoul. Have a nice day.
<christopher.atkins@.e-gineering.com> wrote in message
news:1140685400.006495.6120@.p10g2000cwp.googlegrou ps.com...
> Hello,
> I've got a query that dynamically generates an XML schema based on a
> lookup table. Everything works fine, except that I would like to
> suppress the generation of the namespace declarations on the individual
> elements in xml type created by the sub-select.
>
> Here's the code:
> WITH XMLNAMESPACES ('http://tempuri.org/techFitCategories.xsd' AS tns,
> DEFAULT
> 'http://www.w3.org/2001/XMLSchema')
> SELECT 'techFitCategories' AS "@.id",
> 'http://tempuri.org/techFitCategories.xsd' AS
> "@.targetNamespace",
> 'techFitCategory' AS "element/@.name",
> 'tns:category' AS "element/@.type",
> 'category' AS "simpleType/@.name",
> 'string' AS "simpleType/restriction/@.base",
> (SELECT CategoryName AS "@.value"
> FROM SkillCategories
> FOR XML PATH('enumeration'), TYPE) AS
> "simpleType/restriction"
> FOR XML PATH('schema')
>
> Here's the output:
> <schema xmlns="http://www.w3.org/2001/XMLSchema"
> xmlns:tns="http://tempuri.org/techFitCategories.xsd"
> id="techFitCategories"targetNamespace="http://tempuri.org/techFitCategories.xsd">
> <element name="techFitCategory" type="tns:category" />
> <simpleType name="category">
> <restriction base="string">
> <enumeration xmlns="http://www.w3.org/2001/XMLSchema"
> xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="C#" />
> <enumeration xmlns="http://www.w3.org/2001/XMLSchema"
> xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="Java" />
> <enumeration xmlns="http://www.w3.org/2001/XMLSchema"
> xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="Linux" />
> <enumeration xmlns="http://www.w3.org/2001/XMLSchema"
> xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="SQL Server
> 2005" />
> <enumeration xmlns="http://www.w3.org/2001/XMLSchema"
> xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="VB.NET" />
> <enumeration xmlns="http://www.w3.org/2001/XMLSchema"
> xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="WebSphere"
> />
> <enumeration xmlns="http://www.w3.org/2001/XMLSchema"
> xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="Windows
> 2003" />
> </restriction>
> </simpleType>
> </schema>
>
> I'd like to get rid of the namespace declarations on the
> <enumeration../> elements.
>
> Any ideas?
>
> Thanks in advance!
> ca
>
|||Pohwan,
Thank you kindly for your reply. If I parsed your response properly, I
don't believe this does what I'd hoped. It seems whenever you include
an XML column in a SELECT...FOR XML with the WITH NAMESPACES directive,
it will add the namespace declarations to each element of the XML.
I've tried using an xsd prefix on all the schema elements, but this
doesn't prevent it from adding the namespace attributes to the elements
in the nested XML typed column.
The only alternative I can think of is hacking a big nvarchar together:
not exactly the elegant solution I was hoping for. Don't get me wrong,
I've got a valid schema with the original query, but I'm expecting the
enumeration to get significantly large, and the result XML Schema
document would be huge--not a good thing.
If I've completely missed your point, do you think you could provide a
code example?
ca
|||You are right, Christopher. The prefix doesn't remove the namespace. It just
makes the namespace dormant. When prefixed namespaces are unused they remain
meaningless trash, while non-prefixed or default namespaces are part of
element names. Now all you need is ignoring the unused trash. Optionally you
can remove the namespace in some mid-tier such as XSLT.
Earlier I asked same question in beta SQL2005 group. About alternative of
exclude-result-prefixes of XSLT. And I got replied that there is none, IIRC,
by Michael Rys.
Pohwan Han. Seoul. Have a nice day.
"Christopher Atkins" <christopher.atkins@.e-gineering.com> wrote in message
news:1140721205.415683.28260@.u72g2000cwu.googlegro ups.com...
> Pohwan,
> Thank you kindly for your reply. If I parsed your response properly, I
> don't believe this does what I'd hoped. It seems whenever you include
> an XML column in a SELECT...FOR XML with the WITH NAMESPACES directive,
> it will add the namespace declarations to each element of the XML.
> I've tried using an xsd prefix on all the schema elements, but this
> doesn't prevent it from adding the namespace attributes to the elements
> in the nested XML typed column.
> The only alternative I can think of is hacking a big nvarchar together:
> not exactly the elegant solution I was hoping for. Don't get me wrong,
> I've got a valid schema with the original query, but I'm expecting the
> enumeration to get significantly large, and the result XML Schema
> document would be huge--not a good thing.
> If I've completely missed your point, do you think you could provide a
> code example?
> ca
>
|||We currently output all the namespaces that you provide and at every level
and there is no easy workaround. Note that the XML document is still
correct.
Best is if you file a feature request at
http://lab.msdn.microsoft.com/productfeedback/
by providing sample data, query current result and expected result.
Thanks
Michael
"Han" <hp4444@.kornet.net.korea> wrote in message
news:OP%23rjUQOGHA.3944@.tk2msftngp13.phx.gbl...
> You are right, Christopher. The prefix doesn't remove the namespace. It
> just makes the namespace dormant. When prefixed namespaces are unused they
> remain meaningless trash, while non-prefixed or default namespaces are
> part of element names. Now all you need is ignoring the unused trash.
> Optionally you can remove the namespace in some mid-tier such as XSLT.
> Earlier I asked same question in beta SQL2005 group. About alternative of
> exclude-result-prefixes of XSLT. And I got replied that there is none,
> IIRC, by Michael Rys.
> --
> Pohwan Han. Seoul. Have a nice day.
> "Christopher Atkins" <christopher.atkins@.e-gineering.com> wrote in message
> news:1140721205.415683.28260@.u72g2000cwu.googlegro ups.com...
>
How to suppress namespace attributes in nested XML?
Hello,
I've got a query that dynamically generates an XML schema based on a
lookup table. Everything works fine, except that I would like to
suppress the generation of the namespace declarations on the individual
elements in xml type created by the sub-select.
Here's the code:
WITH XMLNAMESPACES ('http://tempuri.org/techFitCategories.xsd' AS tns,
DEFAULT
'http://www.w3.org/2001/XMLSchema')
SELECT 'techFitCategories' AS "@.id",
'http://tempuri.org/techFitCategories.xsd' AS
"@.targetNamespace",
'techFitCategory' AS "element/@.name",
'tns:category' AS "element/@.type",
'category' AS "simpleType/@.name",
'string' AS "simpleType/restriction/@.base",
(SELECT CategoryName AS "@.value"
FROM SkillCategories
FOR XML PATH('enumeration'), TYPE) AS
"simpleType/restriction"
FOR XML PATH('schema')
Here's the output:
<schema xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:tns="http://tempuri.org/techFitCategories.xsd"
id="techFitCategories"targetNamespace="http://tempuri.org/techFitCategories.
xsd">
<element name="techFitCategory" type="tns:category" />
<simpleType name="category">
<restriction base="string">
<enumeration xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="C#" />
<enumeration xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="Java" />
<enumeration xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="Linux" />
<enumeration xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="SQL Server
2005" />
<enumeration xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="VB.NET" />
<enumeration xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="WebSphere"
/>
<enumeration xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="Windows
2003" />
</restriction>
</simpleType>
</schema>
I'd like to get rid of the namespace declarations on the
<enumeration../> elements.
Any ideas?
Thanks in advance!
caOne way is add a prefix to the default namespace. And add the prefix to
every nodes wherever you want. This is the way many experts recommend.
Pohwan Han. Seoul. Have a nice day.
<christopher.atkins@.e-gineering.com> wrote in message
news:1140685400.006495.6120@.p10g2000cwp.googlegroups.com...
> Hello,
> I've got a query that dynamically generates an XML schema based on a
> lookup table. Everything works fine, except that I would like to
> suppress the generation of the namespace declarations on the individual
> elements in xml type created by the sub-select.
>
> Here's the code:
> WITH XMLNAMESPACES ('http://tempuri.org/techFitCategories.xsd' AS tns,
> DEFAULT
> 'http://www.w3.org/2001/XMLSchema')
> SELECT 'techFitCategories' AS "@.id",
> 'http://tempuri.org/techFitCategories.xsd' AS
> "@.targetNamespace",
> 'techFitCategory' AS "element/@.name",
> 'tns:category' AS "element/@.type",
> 'category' AS "simpleType/@.name",
> 'string' AS "simpleType/restriction/@.base",
> (SELECT CategoryName AS "@.value"
> FROM SkillCategories
> FOR XML PATH('enumeration'), TYPE) AS
> "simpleType/restriction"
> FOR XML PATH('schema')
>
> Here's the output:
> <schema xmlns="http://www.w3.org/2001/XMLSchema"
> xmlns:tns="http://tempuri.org/techFitCategories.xsd"
> id="techFitCategories"targetNamespace="http://tempuri.org/techFitCategorie
s.xsd">
> <element name="techFitCategory" type="tns:category" />
> <simpleType name="category">
> <restriction base="string">
> <enumeration xmlns="http://www.w3.org/2001/XMLSchema"
> xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="C#" />
> <enumeration xmlns="http://www.w3.org/2001/XMLSchema"
> xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="Java" />
> <enumeration xmlns="http://www.w3.org/2001/XMLSchema"
> xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="Linux" />
> <enumeration xmlns="http://www.w3.org/2001/XMLSchema"
> xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="SQL Server
> 2005" />
> <enumeration xmlns="http://www.w3.org/2001/XMLSchema"
> xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="VB.NET" />
> <enumeration xmlns="http://www.w3.org/2001/XMLSchema"
> xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="WebSphere"
> />
> <enumeration xmlns="http://www.w3.org/2001/XMLSchema"
> xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="Windows
> 2003" />
> </restriction>
> </simpleType>
> </schema>
>
> I'd like to get rid of the namespace declarations on the
> <enumeration../> elements.
>
> Any ideas?
>
> Thanks in advance!
> ca
>|||Pohwan,
Thank you kindly for your reply. If I parsed your response properly, I
don't believe this does what I'd hoped. It seems whenever you include
an XML column in a SELECT...FOR XML with the WITH NAMESPACES directive,
it will add the namespace declarations to each element of the XML.
I've tried using an xsd prefix on all the schema elements, but this
doesn't prevent it from adding the namespace attributes to the elements
in the nested XML typed column.
The only alternative I can think of is hacking a big nvarchar together:
not exactly the elegant solution I was hoping for. Don't get me wrong,
I've got a valid schema with the original query, but I'm expecting the
enumeration to get significantly large, and the result XML Schema
document would be huge--not a good thing.
If I've completely missed your point, do you think you could provide a
code example?
ca|||You are right, Christopher. The prefix doesn't remove the namespace. It just
makes the namespace dormant. When prefixed namespaces are unused they remain
meaningless trash, while non-prefixed or default namespaces are part of
element names. Now all you need is ignoring the unused trash. Optionally you
can remove the namespace in some mid-tier such as XSLT.
Earlier I asked same question in beta SQL2005 group. About alternative of
exclude-result-prefixes of XSLT. And I got replied that there is none, IIRC,
by Michael Rys.
Pohwan Han. Seoul. Have a nice day.
"Christopher Atkins" <christopher.atkins@.e-gineering.com> wrote in message
news:1140721205.415683.28260@.u72g2000cwu.googlegroups.com...
> Pohwan,
> Thank you kindly for your reply. If I parsed your response properly, I
> don't believe this does what I'd hoped. It seems whenever you include
> an XML column in a SELECT...FOR XML with the WITH NAMESPACES directive,
> it will add the namespace declarations to each element of the XML.
> I've tried using an xsd prefix on all the schema elements, but this
> doesn't prevent it from adding the namespace attributes to the elements
> in the nested XML typed column.
> The only alternative I can think of is hacking a big nvarchar together:
> not exactly the elegant solution I was hoping for. Don't get me wrong,
> I've got a valid schema with the original query, but I'm expecting the
> enumeration to get significantly large, and the result XML Schema
> document would be huge--not a good thing.
> If I've completely missed your point, do you think you could provide a
> code example?
> ca
>|||We currently output all the namespaces that you provide and at every level
and there is no easy workaround. Note that the XML document is still
correct.
Best is if you file a feature request at
http://lab.msdn.microsoft.com/productfeedback/
by providing sample data, query current result and expected result.
Thanks
Michael
"Han" <hp4444@.kornet.net.korea> wrote in message
news:OP%23rjUQOGHA.3944@.tk2msftngp13.phx.gbl...
> You are right, Christopher. The prefix doesn't remove the namespace. It
> just makes the namespace dormant. When prefixed namespaces are unused they
> remain meaningless trash, while non-prefixed or default namespaces are
> part of element names. Now all you need is ignoring the unused trash.
> Optionally you can remove the namespace in some mid-tier such as XSLT.
> Earlier I asked same question in beta SQL2005 group. About alternative of
> exclude-result-prefixes of XSLT. And I got replied that there is none,
> IIRC, by Michael Rys.
> --
> Pohwan Han. Seoul. Have a nice day.
> "Christopher Atkins" <christopher.atkins@.e-gineering.com> wrote in message
> news:1140721205.415683.28260@.u72g2000cwu.googlegroups.com...
>
I've got a query that dynamically generates an XML schema based on a
lookup table. Everything works fine, except that I would like to
suppress the generation of the namespace declarations on the individual
elements in xml type created by the sub-select.
Here's the code:
WITH XMLNAMESPACES ('http://tempuri.org/techFitCategories.xsd' AS tns,
DEFAULT
'http://www.w3.org/2001/XMLSchema')
SELECT 'techFitCategories' AS "@.id",
'http://tempuri.org/techFitCategories.xsd' AS
"@.targetNamespace",
'techFitCategory' AS "element/@.name",
'tns:category' AS "element/@.type",
'category' AS "simpleType/@.name",
'string' AS "simpleType/restriction/@.base",
(SELECT CategoryName AS "@.value"
FROM SkillCategories
FOR XML PATH('enumeration'), TYPE) AS
"simpleType/restriction"
FOR XML PATH('schema')
Here's the output:
<schema xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:tns="http://tempuri.org/techFitCategories.xsd"
id="techFitCategories"targetNamespace="http://tempuri.org/techFitCategories.
xsd">
<element name="techFitCategory" type="tns:category" />
<simpleType name="category">
<restriction base="string">
<enumeration xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="C#" />
<enumeration xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="Java" />
<enumeration xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="Linux" />
<enumeration xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="SQL Server
2005" />
<enumeration xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="VB.NET" />
<enumeration xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="WebSphere"
/>
<enumeration xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="Windows
2003" />
</restriction>
</simpleType>
</schema>
I'd like to get rid of the namespace declarations on the
<enumeration../> elements.
Any ideas?
Thanks in advance!
caOne way is add a prefix to the default namespace. And add the prefix to
every nodes wherever you want. This is the way many experts recommend.
Pohwan Han. Seoul. Have a nice day.
<christopher.atkins@.e-gineering.com> wrote in message
news:1140685400.006495.6120@.p10g2000cwp.googlegroups.com...
> Hello,
> I've got a query that dynamically generates an XML schema based on a
> lookup table. Everything works fine, except that I would like to
> suppress the generation of the namespace declarations on the individual
> elements in xml type created by the sub-select.
>
> Here's the code:
> WITH XMLNAMESPACES ('http://tempuri.org/techFitCategories.xsd' AS tns,
> DEFAULT
> 'http://www.w3.org/2001/XMLSchema')
> SELECT 'techFitCategories' AS "@.id",
> 'http://tempuri.org/techFitCategories.xsd' AS
> "@.targetNamespace",
> 'techFitCategory' AS "element/@.name",
> 'tns:category' AS "element/@.type",
> 'category' AS "simpleType/@.name",
> 'string' AS "simpleType/restriction/@.base",
> (SELECT CategoryName AS "@.value"
> FROM SkillCategories
> FOR XML PATH('enumeration'), TYPE) AS
> "simpleType/restriction"
> FOR XML PATH('schema')
>
> Here's the output:
> <schema xmlns="http://www.w3.org/2001/XMLSchema"
> xmlns:tns="http://tempuri.org/techFitCategories.xsd"
> id="techFitCategories"targetNamespace="http://tempuri.org/techFitCategorie
s.xsd">
> <element name="techFitCategory" type="tns:category" />
> <simpleType name="category">
> <restriction base="string">
> <enumeration xmlns="http://www.w3.org/2001/XMLSchema"
> xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="C#" />
> <enumeration xmlns="http://www.w3.org/2001/XMLSchema"
> xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="Java" />
> <enumeration xmlns="http://www.w3.org/2001/XMLSchema"
> xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="Linux" />
> <enumeration xmlns="http://www.w3.org/2001/XMLSchema"
> xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="SQL Server
> 2005" />
> <enumeration xmlns="http://www.w3.org/2001/XMLSchema"
> xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="VB.NET" />
> <enumeration xmlns="http://www.w3.org/2001/XMLSchema"
> xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="WebSphere"
> />
> <enumeration xmlns="http://www.w3.org/2001/XMLSchema"
> xmlns:tns="http://tempuri.org/techFitCategories.xsd" value="Windows
> 2003" />
> </restriction>
> </simpleType>
> </schema>
>
> I'd like to get rid of the namespace declarations on the
> <enumeration../> elements.
>
> Any ideas?
>
> Thanks in advance!
> ca
>|||Pohwan,
Thank you kindly for your reply. If I parsed your response properly, I
don't believe this does what I'd hoped. It seems whenever you include
an XML column in a SELECT...FOR XML with the WITH NAMESPACES directive,
it will add the namespace declarations to each element of the XML.
I've tried using an xsd prefix on all the schema elements, but this
doesn't prevent it from adding the namespace attributes to the elements
in the nested XML typed column.
The only alternative I can think of is hacking a big nvarchar together:
not exactly the elegant solution I was hoping for. Don't get me wrong,
I've got a valid schema with the original query, but I'm expecting the
enumeration to get significantly large, and the result XML Schema
document would be huge--not a good thing.
If I've completely missed your point, do you think you could provide a
code example?
ca|||You are right, Christopher. The prefix doesn't remove the namespace. It just
makes the namespace dormant. When prefixed namespaces are unused they remain
meaningless trash, while non-prefixed or default namespaces are part of
element names. Now all you need is ignoring the unused trash. Optionally you
can remove the namespace in some mid-tier such as XSLT.
Earlier I asked same question in beta SQL2005 group. About alternative of
exclude-result-prefixes of XSLT. And I got replied that there is none, IIRC,
by Michael Rys.
Pohwan Han. Seoul. Have a nice day.
"Christopher Atkins" <christopher.atkins@.e-gineering.com> wrote in message
news:1140721205.415683.28260@.u72g2000cwu.googlegroups.com...
> Pohwan,
> Thank you kindly for your reply. If I parsed your response properly, I
> don't believe this does what I'd hoped. It seems whenever you include
> an XML column in a SELECT...FOR XML with the WITH NAMESPACES directive,
> it will add the namespace declarations to each element of the XML.
> I've tried using an xsd prefix on all the schema elements, but this
> doesn't prevent it from adding the namespace attributes to the elements
> in the nested XML typed column.
> The only alternative I can think of is hacking a big nvarchar together:
> not exactly the elegant solution I was hoping for. Don't get me wrong,
> I've got a valid schema with the original query, but I'm expecting the
> enumeration to get significantly large, and the result XML Schema
> document would be huge--not a good thing.
> If I've completely missed your point, do you think you could provide a
> code example?
> ca
>|||We currently output all the namespaces that you provide and at every level
and there is no easy workaround. Note that the XML document is still
correct.
Best is if you file a feature request at
http://lab.msdn.microsoft.com/productfeedback/
by providing sample data, query current result and expected result.
Thanks
Michael
"Han" <hp4444@.kornet.net.korea> wrote in message
news:OP%23rjUQOGHA.3944@.tk2msftngp13.phx.gbl...
> You are right, Christopher. The prefix doesn't remove the namespace. It
> just makes the namespace dormant. When prefixed namespaces are unused they
> remain meaningless trash, while non-prefixed or default namespaces are
> part of element names. Now all you need is ignoring the unused trash.
> Optionally you can remove the namespace in some mid-tier such as XSLT.
> Earlier I asked same question in beta SQL2005 group. About alternative of
> exclude-result-prefixes of XSLT. And I got replied that there is none,
> IIRC, by Michael Rys.
> --
> Pohwan Han. Seoul. Have a nice day.
> "Christopher Atkins" <christopher.atkins@.e-gineering.com> wrote in message
> news:1140721205.415683.28260@.u72g2000cwu.googlegroups.com...
>
Friday, February 24, 2012
How to store and use xqueries in sql2005?
Hello
I am trying to use the xml.query() method to output xml. Is there any way of storing the xqueries themselves in the database?
This works:
SELECT Col.query('
<Root>
<Header>
{
for $e in Report/PolicyBatchRef
return $e/PolicyBatchRef
}
</Header>
<NewElement>
{
for $e in Report/PolicyBatchRef/Locations/Location
return $e
}
</NewElement>
</Root>
')
FROM xmltest where id = 1
but this doesn't:
declare @.xquery nvarchar(max)
set @.xquery = '<Root>
<Header>
{
for $e in Report/PolicyBatchRef
return $e/PolicyBatchRef
}
</Header>
<NewElement>
{
for $e in Report/PolicyBatchRef/Locations/Location
return $e
}
</NewElement>
</Root>'
SELECT col.query(@.xquery) from xmltest where id = 1
I get the error
Msg 8172, Level 16, State 1, Line 4
The argument 1 of the xml data type method "query" must be a string literal.
Same thing happens when I store the xquery in the DB.
Any ideas?
Thanks very much
The string argument to the query function must be a string literal, so you cannot pass it in as a parameter. You have a few options:
1. Create UDF's that encapsulate the SELECT and the xquery, and invoke these.
2. Store strings that represent the SELECT and the xquery and invoke them at runtime using sp_executesql
3. store the strings that represent the xquery and combine them with the strings for the SELECT statement, and execute with sp_executesql. This has the most potential for SQL injection since you are constructing SQL dynamically with string concatenation. This method should be used only if the other two ways cannot be used.
|||You can dynamically create XPath queries using sql variables.
I have successfully used something like
declare @.Date varchar(10)
set @.Date = replace(convert(varchar(10), getdate(), 121), '-', '')
WITH XMLNAMESPACES( 'https://www,somewhere.com/Bureau' AS "Bureau")
SELECT
AggDefaultAmount = convert(varchar(50), ResponseXML.query('sum(/BureauResponse/Bureau:ND07/Bureau:ND07/Bureau:Amount[../Bureau:InformationDate<sql:variable("@.Date")])'))
FROM
DB..testxml WITH (NOLOCK)
Adapt adopt and improve.
|||
Hi,
I need to a have a Store procedure that takes xml as input and it stores in my database tables. Using the nodes() method and value() method, I am able to solve this but only issue I have is these methods take arguments only as string literals. So, I have to hard code the xquery in the SP. I expect to read the xquery from a table and fetch it in a variable within the SP and pass it as the parameter to the Value() and nodes() method.
Please advice.
Sample code I have as below
declare @.xmldoc xml
SET @.xmldoc = '<customer><name>John</name><city>New York</city></customer>'
--This select works
SELECT
T.C.value('name[1]',varchar(50))
T.C.value('city[1]',varchar(50))
FROM @.xmldoc.nodes('/customer') AS T(C)
--But this does not work when I try to specify xquery using a variable as below
declare @.xquery_name varchar(100),@.xquery_city varchar(100), @.xquery_cust varchar(100)
SELECT @.xquery_name = 'name[1]', @.xquery_city = 'city[1]', @.xquery_cust = '/customer'
SELECT
T.C.value(@.xquery_name ,varchar(50))
T.C.value('@.xquery_city',varchar(50))
FROM @.xmldoc.nodes(@.xquery_cust) AS T(C)
Please help me out.
How to store and use xqueries in sql2005?
Hello
I am trying to use the xml.query() method to output xml. Is there any way of storing the xqueries themselves in the database?
This works:
SELECT Col.query('
<Root>
<Header>
{
for $e in Report/PolicyBatchRef
return $e/PolicyBatchRef
}
</Header>
<NewElement>
{
for $e in Report/PolicyBatchRef/Locations/Location
return $e
}
</NewElement>
</Root>
')
FROM xmltest where id = 1
but this doesn't:
declare @.xquery nvarchar(max)
set @.xquery = '<Root>
<Header>
{
for $e in Report/PolicyBatchRef
return $e/PolicyBatchRef
}
</Header>
<NewElement>
{
for $e in Report/PolicyBatchRef/Locations/Location
return $e
}
</NewElement>
</Root>'
SELECT col.query(@.xquery) from xmltest where id = 1
I get the error
Msg 8172, Level 16, State 1, Line 4
The argument 1 of the xml data type method "query" must be a string literal.
Same thing happens when I store the xquery in the DB.
Any ideas?
Thanks very much
The string argument to the query function must be a string literal, so you cannot pass it in as a parameter. You have a few options:
1. Create UDF's that encapsulate the SELECT and the xquery, and invoke these.
2. Store strings that represent the SELECT and the xquery and invoke them at runtime using sp_executesql
3. store the strings that represent the xquery and combine them with the strings for the SELECT statement, and execute with sp_executesql. This has the most potential for SQL injection since you are constructing SQL dynamically with string concatenation. This method should be used only if the other two ways cannot be used.
|||You can dynamically create XPath queries using sql variables.
I have successfully used something like
declare @.Date varchar(10)
set @.Date = replace(convert(varchar(10), getdate(), 121), '-', '')
WITH XMLNAMESPACES( 'https://www,somewhere.com/Bureau' AS "Bureau")
SELECT
AggDefaultAmount = convert(varchar(50), ResponseXML.query('sum(/BureauResponse/Bureau:ND07/Bureau:ND07/Bureau:Amount[../Bureau:InformationDate<sql:variable("@.Date")])'))
FROM
DB..testxml WITH (NOLOCK)
Adapt adopt and improve.
|||
Hi,
I need to a have a Store procedure that takes xml as input and it stores in my database tables. Using the nodes() method and value() method, I am able to solve this but only issue I have is these methods take arguments only as string literals. So, I have to hard code the xquery in the SP. I expect to read the xquery from a table and fetch it in a variable within the SP and pass it as the parameter to the Value() and nodes() method.
Please advice.
Sample code I have as below
declare @.xmldoc xml
SET @.xmldoc = '<customer><name>John</name><city>New York</city></customer>'
--This select works
SELECT
T.C.value('name[1]',varchar(50))
T.C.value('city[1]',varchar(50))
FROM @.xmldoc.nodes('/customer') AS T(C)
--But this does not work when I try to specify xquery using a variable as below
declare @.xquery_name varchar(100),@.xquery_city varchar(100), @.xquery_cust varchar(100)
SELECT @.xquery_name = 'name[1]', @.xquery_city = 'city[1]', @.xquery_cust = '/customer'
SELECT
T.C.value(@.xquery_name ,varchar(50))
T.C.value('@.xquery_city',varchar(50))
FROM @.xmldoc.nodes(@.xquery_cust) AS T(C)
Please help me out.
Subscribe to:
Posts (Atom)