SQL For XML PATH - Redundant xmlns throughout result and how to return a node with an attribute AND value

Question

Removing all the extraneous information from my initial posting and focusing on two items:

The majority of my data is just <element>value</element> but certain parts call out these namespace prefixes:

<componentList>
    <InstantiatedBillingRateComponent>
        <definition xsi:type="PSPM">
          ...
        </definition>

Looking at this as a model - How to return XML from SQL Server 2008 that is structured with multiple selections sharing a common parent

Select BillRateType as "@xsi:type" ... for xml path('definition'),type,elements

which gave me the error: XML name space prefix 'xsi' declaration is missing for FOR XML column name '@xsi:type'.

So I did some research on the WITH xmlnamespaces clause and added this to the beginning of the query: WITH xmlnamespaces ('http://www.w3.org/2001/XMLSchema-instance' as xsi, 'http://www.w3.org/2001/XMLSchema' AS xsd )

I received no error but got this:

<definition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="PSPM">

and received both xmlns: declarations ALL throughout the entire result (looks like every nested level at the XML PATH or Root if called out)

Problem 1: How can I get rid of all the xmlns (except the top level)

Problem 2: If my column called BillCat has the value COBRA how do I achieve this output?

<billingCategory>
    <ID xsi:type="xsd:string">COBRA</ID>
    <ID xsi:type="xsd:string">BillingCategory</ID>
</billingCategory>

Update: For this problem 2 I am getting closer - I'm unsure how to mix the element attributes and values together.

If I execute this:

WITH xmlnamespaces ('...://www.w3.org/2001/XMLSchema-instance' as xsi, '...://www.w3.org/2001/XMLSchema' AS xsd )
select 
    top 1
    'xsd:string' as "@xsi:type"
    ,BillCat
from
    ##chris_global
for xml path('ID'),type,elements,root('billingCategory')

I get:

<billingCategory xmlns:xsd="....://www.w3.org/2001/XMLSchema" xmlns:xsi="...://www.w3.org/2001/XMLSchema-instance">
  <ID xsi:type="xsd:string">
    <BillCat>COBRA</BillCat>
  </ID>
</billingCategory>

but i want:

<billingCategory>
    <ID xsi:type="xsd:string">COBRA</ID>
    <ID xsi:type="xsd:string">BillingCategory</ID>
</billingCategory>

I keep looking for query samples that show element with an attribute AND element value like above but cannot find one.

Update -

I found something that seems to work from: [can't post]

WITH xmlnamespaces ('http://www.w3.org/2001/XMLSchema-instance' as xsi, 'http://www.w3.org/2001/XMLSchema' AS xsd )
select 
    top 1
    'xsd:string' as "@xsi:type"
    ,BillCat as 'text()'
from
    ##chris_global
for xml path('ID'),type,elements,root('billingCategory')

gave me:

<billingCategory xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <ID xsi:type="xsd:string">COBRA</ID>
</billingCategory>

So I guess I'm just looking for ways to remove the redundant xmlns from everything (short of parsing it out after the fact)

SQL For XML PATH - Redundant xmlns throughout result and how to return a node with an attribute AND value

0 Answers0