Remove namespace in each child node in xml output using sql

• Kasinathan

  SSC-Addicted

  Points: 426

  More actions

  July 14, 2016 at 7:33 am

  #327864

  Hi all,

  i am generating one xml columns. in that root i am having some namespaces. my query is i dont want to display the namespace in each chile node. i just want to display it in only the root.

  below is the query

  WITH XMLNAMESPACES ( 'http://www.w3.org/2001/XMLSchema-instance' as xsi,

  'urn:un:unece:uncefact:data:specification:UnqualifiedDataTypesSchemaModule:2' as udt,

  'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' as cbc,

  'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' as cac,

  DEFAULT 'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2')

  SELECT 'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2.0 UBL-Invoice-2.0.xsd' as '@xsi:schemaLocation',

  'NoFacture' as 'cbc:ID',

  CONVERT(char(10), GETUTCDATE(),126) as 'cbc:IssueDate',

  308 as 'cbc:InvoiceTypeCode',

  'EUR' as 'cbc:DocumentCurrencyCode',

  (select NEWID() FOR XML PATH('cbc:ID'), TYPE) AS [cac:AdditionalDocumentReference] ,

  (select 'application/zip' as '@mimeCode', ' ' FOR XML PATH ('cbc.EmbeddedDocumentBinaryObject'),TYPE) as [cac:AdditionalDocumentReference/cac:Attachment],

  (select 'ID' for XML path ('cbc:ID'), Type) as [cac:AccountingSupplierParty/cac:Party/cac:PartyIdentification],

  (select 'Name' for XML path ('cbc:Name'), Type) as [cac:AccountingSupplierParty/cac:Party/cac:PartyName],

  (select 'PostalZone' for xml path ('cbc:PostalZone'), type) as [cac:AccountingSupplierParty/cac:Party/cac:PostalAddress],

  (select 'Line' for xml path ('cbc:Line'), type) as [cac:AccountingSupplierParty/cac:Party/cac:PostalAddress/cac:AddressLine],

  (select 'CompanyID' for xml path ('cbc:CompanyID'), type) as [cac:AccountingSupplierParty/cac:Party/cac:PostalAddress/cac:PartyTaxScheme],

  (select 'RegistrationName' for xml path ('cbc:RegistrationName'), type) as [cac:AccountingSupplierParty/cac:Party/cac:PostalAddress/cac:PartyLegalEntity],

  (select 'CompanyID' for xml path ('cbc:CompanyID'), type) as [cac:AccountingSupplierParty/cac:Party/cac:PostalAddress/cac:PartyLegalEntity],

  (select 'PostalZone' for xml path ('cbc:PostalZone'), type) as [cac:AccountingSupplierParty/cac:Party/cac:PostalAddress/cac:PartyLegalEntity/cac:RegistrationAddress],

  (select 'Line' for xml path ('cbc:Line'), type) as [cac:AccountingSupplierParty/cac:Party/cac:PostalAddress/cac:PartyLegalEntity/cac:RegistrationAddress/cac:AddressLine],

  (select 'Name' for xml path ('cbc:Name'), type) as [cac:AccountingCustomerParty/cac:Party/cac:PartyName],

  (select 'Line' for xml path ('cbc:Line'), type) as [cac:AccountingCustomerParty/cac:Party/cac:PostalAddress/cac:AddressLine],

  (select 'NES' as '@listAgencyID','Northern European Subset' as '@listAgencyName', 'UN/ECE 4461 Subset' as '@listID', 'Payment Means' as '@listName' for xml path('cbc:PaymentMeansCode'), type ) as [cac:PaymentMeans],

  (select 'TaxAmount' for xml path ('cbc:TaxAmount'), Type ) as [cac:TaxTotal],

  (select 'TaxInclusiveAmount' for xml path ('cbc:TaxInclusiveAmount'), Type ) as [cac:LegalMonetaryTotal],

  (select 'TaxExclusiveAmount' for xml path ('cbc:TaxExclusiveAmount'), Type ) as [cac:LegalMonetaryTotal],

  (select 'PayableAmount' for xml path ('cbc:PayableAmount'), Type ) as [cac:LegalMonetaryTotal],

  (select 'ID' for xml path ('cbc:ID'), Type ) as [cac:InvoiceLine],

  (select 'InvoicedQuantity' for xml path ('cbc:InvoicedQuantity'), Type ) as [cac:InvoiceLine],

  'EUR' as 'cbc:LineExtensionAmount',

  (select 'Name' for xml path ('cbc:Name'),type ) as [cac:Item/cac:AdditionalItemProperty],

  (select 'Value' for xml path ('cbc:Value'),type ) as [cac:Item/cac:AdditionalItemProperty],

  (select 'PriceAmount' for xml path ('cbc:PriceAmount'),type ) as [cac:Price],

  (select 'BaseQuantity' for xml path ('cbc:BaseQuantity'),type ) as [cac:Price]

  FOR XML Path('Root'),TYPE

• GSquared

  SSC Guru

  Points: 260824

  More actions

  July 14, 2016 at 7:39 am

  #1890104

  I dug into this one time, and I think the only solution I came up with was to convert the XML to VARCHAR(MAX) and do string manipulation on it. A regex CLR UDF could probably do it more efficiently, but I'm not up to that level of CLR.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  July 14, 2016 at 5:19 pm

  #1890216

  Unless you have dumbed something down drastically and this isn't really what you are trying to do, this is far simpler and does what you want:

  GO

  WITH XMLNAMESPACES ( 'http://www.w3.org/2001/XMLSchema-instance' as xsi,

  'urn:un:unece:uncefact:data:specification:UnqualifiedDataTypesSchemaModule:2' as udt,

  'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' as cbc,

  'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' as cac,

  DEFAULT 'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2')

  SELECT 'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2.0 UBL-Invoice-2.0.xsd' as '@xsi:schemaLocation',

  'NoFacture' as 'cbc:ID',

  CONVERT(char(10), GETUTCDATE(),126) as 'cbc:IssueDate',

  308 as 'cbc:InvoiceTypeCode',

  'EUR' as 'cbc:DocumentCurrencyCode',

  NEWID()[cac:AdditionalDocumentReference/cbc:ID] ,

  'application/zip' [cac:AdditionalDocumentReference/cac:Attachment/@mimeCode],

  'ID' [cac:AccountingSupplierParty/cac:Party/cac:PartyIdentification/cbc:ID],

  'Name' [cac:AccountingSupplierParty/cac:Party/cac:PartyName/cbc:Name],

  'PostalZone' [cac:AccountingSupplierParty/cac:Party/cac:PostalAddress/cbc:PostalZone],

  'Line' [cac:AccountingSupplierParty/cac:Party/cac:PostalAddress/cac:AddressLine/cbc:Line],

  'CompanyID' [cac:AccountingSupplierParty/cac:Party/cac:PostalAddress/cac:PartyTaxScheme/cbc:CompanyID],

  'RegistrationName' [cac:AccountingSupplierParty/cac:Party/cac:PostalAddress/cac:PartyLegalEntity/cbc:RegistrationName],

  'CompanyID' [cac:AccountingSupplierParty/cac:Party/cac:PostalAddress/cac:PartyLegalEntity/cbc:CompanyID],

  'PostalZone' [cac:AccountingSupplierParty/cac:Party/cac:PostalAddress/cac:PartyLegalEntity/cac:RegistrationAddress/cbc:PostalZone],

  'Line' [cac:AccountingSupplierParty/cac:Party/cac:PostalAddress/cac:PartyLegalEntity/cac:RegistrationAddress/cac:AddressLine/cbc:Line],

  'Name' [cac:AccountingCustomerParty/cac:Party/cac:PartyName/cbc:Name],

  'Line' [cac:AccountingCustomerParty/cac:Party/cac:PostalAddress/cac:AddressLine/cbc:Line],

  'NES' [cac:PaymentMeans/cbc:PaymentMeansCode/@listAgencyID],

  'Northern European Subset' [cac:PaymentMeans/cbc:PaymentMeansCode/@listAgencyName],

  'UN/ECE 4461 Subset' [cac:PaymentMeans/cbc:PaymentMeansCode/@listID],

  'Payment Means' [cac:PaymentMeans/cbc:PaymentMeansCode/@listName],

  'TaxAmount' [cac:TaxTotal/cbc:TaxAmount],

  'TaxInclusiveAmount' [cac:LegalMonetaryTotal/cbc:TaxInclusiveAmount],

  'TaxExclusiveAmount' [cac:LegalMonetaryTotal/cbc:TaxExclusiveAmount],

  'PayableAmount' [cac:LegalMonetaryTotal/cbc:PayableAmount],

  'ID' [cac:InvoiceLine/cbc:ID],

  'InvoicedQuantity' [cac:InvoiceLine/cbc:InvoicedQuantity],

  'EUR' as 'cbc:LineExtensionAmount',

  'Name' [cac:Item/cac:AdditionalItemProperty/cbc:Name],

  'Value' [cac:Item/cac:AdditionalItemProperty/cbc:Value],

  'PriceAmount' [cac:Price/cbc:PriceAmount],

  'BaseQuantity' [cac:Price/cbc:BaseQuantity]

  FOR XML Path('Root'),TYPE

  <Root xmlns="urn:oasis:names:specification:ubl:schema:xsd:Invoice-2" xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2" xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2" xmlns:udt="urn:un:unece:uncefact:data:specification:UnqualifiedDataTypesSchemaModule:2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:oasis:names:specification:ubl:schema:xsd:Invoice-2.0 UBL-Invoice-2.0.xsd">

  <cbc:ID>NoFacture</cbc:ID>

  <cbc:IssueDate>2016-07-14</cbc:IssueDate>

  <cbc:InvoiceTypeCode>308</cbc:InvoiceTypeCode>

  <cbc:DocumentCurrencyCode>EUR</cbc:DocumentCurrencyCode>

  <cac:AdditionalDocumentReference>

  <cbc:ID>A445AA2B-9258-4C65-B017-59FEF8853B00</cbc:ID>

  <cac:Attachment mimeCode="application/zip" />

  </cac:AdditionalDocumentReference>

  <cac:AccountingSupplierParty>

  <cac:Party>

  <cac:PartyIdentification>

  <cbc:ID>ID</cbc:ID>

  </cac:PartyIdentification>

  <cac:PartyName>

  <cbc:Name>Name</cbc:Name>

  </cac:PartyName>

  <cac:PostalAddress>

  <cbc:PostalZone>PostalZone</cbc:PostalZone>

  <cac:AddressLine>

  <cbc:Line>Line</cbc:Line>

  </cac:AddressLine>

  <cac:PartyTaxScheme>

  <cbc:CompanyID>CompanyID</cbc:CompanyID>

  </cac:PartyTaxScheme>

  <cac:PartyLegalEntity>

  <cbc:RegistrationName>RegistrationName</cbc:RegistrationName>

  <cbc:CompanyID>CompanyID</cbc:CompanyID>

  <cac:RegistrationAddress>

  <cbc:PostalZone>PostalZone</cbc:PostalZone>

  <cac:AddressLine>

  <cbc:Line>Line</cbc:Line>

  </cac:AddressLine>

  </cac:RegistrationAddress>

  </cac:PartyLegalEntity>

  </cac:PostalAddress>

  </cac:Party>

  </cac:AccountingSupplierParty>

  <cac:AccountingCustomerParty>

  <cac:Party>

  <cac:PartyName>

  <cbc:Name>Name</cbc:Name>

  </cac:PartyName>

  <cac:PostalAddress>

  <cac:AddressLine>

  <cbc:Line>Line</cbc:Line>

  </cac:AddressLine>

  </cac:PostalAddress>

  </cac:Party>

  </cac:AccountingCustomerParty>

  <cac:PaymentMeans>

  <cbc:PaymentMeansCode listAgencyID="NES" listAgencyName="Northern European Subset" listID="UN/ECE 4461 Subset" listName="Payment Means" />

  </cac:PaymentMeans>

  <cac:TaxTotal>

  <cbc:TaxAmount>TaxAmount</cbc:TaxAmount>

  </cac:TaxTotal>

  <cac:LegalMonetaryTotal>

  <cbc:TaxInclusiveAmount>TaxInclusiveAmount</cbc:TaxInclusiveAmount>

  <cbc:TaxExclusiveAmount>TaxExclusiveAmount</cbc:TaxExclusiveAmount>

  <cbc:PayableAmount>PayableAmount</cbc:PayableAmount>

  </cac:LegalMonetaryTotal>

  <cac:InvoiceLine>

  <cbc:ID>ID</cbc:ID>

  <cbc:InvoicedQuantity>InvoicedQuantity</cbc:InvoicedQuantity>

  </cac:InvoiceLine>

  <cbc:LineExtensionAmount>EUR</cbc:LineExtensionAmount>

  <cac:Item>

  <cac:AdditionalItemProperty>

  <cbc:Name>Name</cbc:Name>

  <cbc:Value>Value</cbc:Value>

  </cac:AdditionalItemProperty>

  </cac:Item>

  <cac:Price>

  <cbc:PriceAmount>PriceAmount</cbc:PriceAmount>

  <cbc:BaseQuantity>BaseQuantity</cbc:BaseQuantity>

  </cac:Price>

  </Root>

  There doesn't seem to be a purpose/need for all those subqueries.

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]