0

I need to create xml files to migrate a huge amount of data from one database to another database.

How to get result xsd format as below from ms sql script query?

Please share if you have any idea.

The xml file format is below:

<Batch>
    <retail:customer xmlns:core="http://www.bactor.com/core" xmlns:retail=""http://www.bactor.com/retail" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
        <retail:addresses>
            <retail:address>
                <retail:country>GB</retail:country>
                <retail:countryCodeId></retail:countryCodeId>
                <retail:isPreferred>true</retail:isPreferred>
                <retail:isActive>true</retail:isActive>
                <retail:typeId>PERSONAL_ADDRESS</retail:typeId>
                <retail:postCode>2344</retail:postCode>
                <retail:street1>1234214</retail:street1>
                <retail:isTemplate>false</retail:isTemplate>
                <retail:referenceId></retail:referenceId>
                <retail:addressReferenceId>0727-:83f5bd9f331:e8e438a1:fa34668911272008</retail:addressReferenceId>
            </retail:address>
        </retail:addresses>
        <retail:contactMethod></retail:contactMethod>
        <retail:contactable>false</retail:contactable>
        <retail:maritalStatus></retail:maritalStatus>
        <retail:nationality></retail:nationality>
        <retail:noChildren>0</retail:noChildren>
        <retail:customerNumber>1</retail:customerNumber>
        <retail:emailAddresses>
            <retail:emailAddress>alice@wonderland.hearts</retail:emailAddress>
            <retail:preferred>true</retail:preferred>
            <retail:restrictedReason></retail:restrictedReason>
            <retail:status></retail:status>
            <retail:typeId>PERSONAL_EMAIL</retail:typeId>
            <retail:referenceId></retail:referenceId>
            <retail:active>false</retail:active>
        </retail:emailAddresses>
        <retail:phoneNumbers>
            <retail:countryCode></retail:countryCode>
            <retail:number>11222445566</retail:number>
            <retail:preferred>true</retail:preferred>
            <retail:restrictedReason></retail:restrictedReason>
            <retail:status></retail:status>
            <retail:typeId>LANDLINE</retail:typeId>
            <retail:referenceId></retail:referenceId>
            <retail:active>true</retail:active>
            <retail:phoneNumberReferenceId>e437-:83f5bd9f331:e8e438a1:fa34668911272008</retail:phoneNumberReferenceId>
        </retail:phoneNumbers>
        <retail:customerName>
            <retail:surname>AppleGate</retail:surname>
            <retail:forename>Alice</retail:forename>
            <retail:title>Mrs</retail:title>
            <retail:sex>FEMALE</retail:sex>
            <retail:dateOfBirth>2012-09-12T00:00:00+01:00</retail:dateOfBirth>
        </retail:customerName>
        <retail:businessContactMethod></retail:businessContactMethod>
        <retail:preferredContactTime></retail:preferredContactTime>
        <retail:allowInternalMarketing>true</retail:allowInternalMarketing>
        <retail:allowExternalMarketing>true</retail:allowExternalMarketing>
        <retail:employeeKey></retail:employeeKey>
        <retail:customerType>RETAIL</retail:customerType>
        <retail:organisation></retail:organisation>
        <retail:taxIdentification></retail:taxIdentification>
        <retail:companyNumber></retail:companyNumber>
        <retail:createdBy></retail:createdBy>
        <retail:createdAt></retail:createdAt>
        <retail:status>New</retail:status>
        <retail:source></retail:source>
    </retail:customer>
    <retail:customer xmlns:core="http://www.enactor.com/core" xmlns:retail="http://www.enactor.com/retail" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
        <retail:addresses>
            <retail:address>
                <retail:country>GB</retail:country>
                <retail:countryCodeId></retail:countryCodeId>
                <retail:isPreferred>true</retail:isPreferred>
                <retail:isActive>true</retail:isActive>
                <retail:typeId>PERSONAL_ADDRESS</retail:typeId>
                <retail:postCode>2344</retail:postCode>
                <retail:street1>1234214</retail:street1>
                <retail:isTemplate>false</retail:isTemplate>
                <retail:referenceId></retail:referenceId>
                <retail:addressReferenceId>0727-:83f5bd9f331:e8e438a1:fa34668911272008</retail:addressReferenceId>
            </retail:address>
        </retail:addresses>
        <retail:contactMethod></retail:contactMethod>
        <retail:contactable>false</retail:contactable>
        <retail:maritalStatus></retail:maritalStatus>
        <retail:nationality></retail:nationality>
        <retail:noChildren>0</retail:noChildren>
        <retail:customerNumber>1</retail:customerNumber>
        <retail:emailAddresses>
            <retail:emailAddress>alice@wonderland.hearts</retail:emailAddress>
            <retail:preferred>true</retail:preferred>
            <retail:restrictedReason></retail:restrictedReason>
            <retail:status></retail:status>
            <retail:typeId>PERSONAL_EMAIL</retail:typeId>
            <retail:referenceId></retail:referenceId>
            <retail:active>false</retail:active>
        </retail:emailAddresses>
        <retail:phoneNumbers>
            <retail:countryCode></retail:countryCode>
            <retail:number>11222445566</retail:number>
            <retail:preferred>true</retail:preferred>
            <retail:restrictedReason></retail:restrictedReason>
            <retail:status></retail:status>
            <retail:typeId>LANDLINE</retail:typeId>
            <retail:referenceId></retail:referenceId>
            <retail:active>true</retail:active>
            <retail:phoneNumberReferenceId>e437-:83f5bd9f331:e8e438a1:fa34668911272008</retail:phoneNumberReferenceId>
        </retail:phoneNumbers>
        <retail:customerName>
            <retail:surname>AppleGate</retail:surname>
            <retail:forename>Alice</retail:forename>
            <retail:title>Mrs</retail:title>
            <retail:sex>FEMALE</retail:sex>
            <retail:dateOfBirth>2012-09-12T00:00:00+01:00</retail:dateOfBirth>
        </retail:customerName>
        <retail:businessContactMethod></retail:businessContactMethod>
        <retail:preferredContactTime></retail:preferredContactTime>
        <retail:allowInternalMarketing>true</retail:allowInternalMarketing>
        <retail:allowExternalMarketing>true</retail:allowExternalMarketing>
        <retail:employeeKey></retail:employeeKey>
        <retail:customerType>RETAIL</retail:customerType>
        <retail:organisation></retail:organisation>
        <retail:taxIdentification></retail:taxIdentification>
        <retail:companyNumber></retail:companyNumber>
        <retail:createdBy></retail:createdBy>
        <retail:createdAt></retail:createdAt>
        <retail:status>New</retail:status>
        <retail:source></retail:source>
    </retail:customer>
    </Batch>

I am also trying with the below a pieces of sql script. but it is not working.

WITH XMLNAMESPACES ('http://www.enactor.com/retail' as ns1)
SELECT top 100 [id] 
      ,[Title] 
      ,[First_Name] 
      , RowNum = Row_NUMBER() OVER (Order by id) 
  FROM [Firinne].[dbo].[Contact] as Customer
  For XML PATH('retail:Customer')
Tun
  • 824
  • 3
  • 16
  • 30

1 Answers1

0

Try this:

DECLARE @xml XML

;WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema-instance' AS xsi, 'http://www.bactor.com/retail' AS retail, 'http://www.bactor.com/core' AS core)
SELECT @xml = (SELECT 
[retail:Customer].id AS [retail:id],
[retail:Customer].contactMethod AS [retail:contactMethod],
[retail:Customer].contactable AS [retail:contactable],
[retail:address].emailAddress AS [retail:emailAddress],
[retail:address].typeId AS [retail:typeId]

FROM dbo.Customers AS [retail:Customer]
JOIN dbo.emailAddresses AS [retail:address] ON [retail:Customer].id = [retail:address].customerID
FOR XML AUTO, ELEMENTS)

SET @xml = '<Batch>' + CAST(@xml AS NVARCHAR(max)) + '</Batch>'
SELECT @xml

Output:

<Batch>
  <retail:Customer xmlns:core="http://www.bactor.com/core" xmlns:retail="http://www.bactor.com/retail" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <retail:id>1</retail:id>
    <retail:contactMethod>1</retail:contactMethod>
    <retail:contactable>1</retail:contactable>
    <retail:address>
      <retail:emailAddress>some@some.some</retail:emailAddress>
      <retail:typeId>1</retail:typeId>
    </retail:address>
    <retail:address>
      <retail:emailAddress>some@some.some</retail:emailAddress>
      <retail:typeId>2</retail:typeId>
    </retail:address>
  </retail:Customer>
  <retail:Customer xmlns:core="http://www.bactor.com/core" xmlns:retail="http://www.bactor.com/retail" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <retail:id>2</retail:id>
    <retail:contactMethod>1</retail:contactMethod>
    <retail:contactable>0</retail:contactable>
    <retail:address>
      <retail:emailAddress>some@some.some</retail:emailAddress>
      <retail:typeId>3</retail:typeId>
    </retail:address>
  </retail:Customer>
</Batch>

EDIT:

You can do it like:

DECLARE @xml XML

;WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema-instance' AS xsi, 'http://www.bactor.com/retail' AS retail, 'http://www.bactor.com/core' AS core)
SELECT @xml = (SELECT 
[retail:Customer].id AS [retail:id],
[retail:Customer].contactMethod AS [retail:contactMethod],
[retail:Customer].contactable AS [retail:contactable],

(SELECT 
[retail:address].emailAddress ,
[retail:address].typeId
FROM dbo.emailAddresses AS [retail:address] WHERE [retail:Customer].id = [retail:address].customerID
FOR XML AUTO, TYPE, ELEMENTS, ROOT('retail:Addresses')
)
FROM dbo.Customers AS [retail:Customer]
FOR XML AUTO, ELEMENTS)

SET @xml = '<Batch>' + CAST(@xml AS NVARCHAR(max)) + '</Batch>'
SELECT @xml

But namespaces are added to all child:

<Batch>
  <retail:Customer xmlns:core="http://www.bactor.com/core" xmlns:retail="http://www.bactor.com/retail" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <retail:id>1</retail:id>
    <retail:contactMethod>1</retail:contactMethod>
    <retail:contactable>1</retail:contactable>
    <retail:Addresses xmlns:core="http://www.bactor.com/core" xmlns:retail="http://www.bactor.com/retail" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <retail:address>
        <emailAddress>some@some.some</emailAddress>
        <typeId>1</typeId>
      </retail:address>
      <retail:address>
        <emailAddress>some@some.some</emailAddress>
        <typeId>2</typeId>
      </retail:address>
    </retail:Addresses>
  </retail:Customer>
  <retail:Customer xmlns:core="http://www.bactor.com/core" xmlns:retail="http://www.bactor.com/retail" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <retail:id>2</retail:id>
    <retail:contactMethod>1</retail:contactMethod>
    <retail:contactable>0</retail:contactable>
    <retail:Addresses xmlns:core="http://www.bactor.com/core" xmlns:retail="http://www.bactor.com/retail" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <retail:address>
        <emailAddress>some@some.some</emailAddress>
        <typeId>3</typeId>
      </retail:address>
    </retail:Addresses>
  </retail:Customer>
</Batch>

It is a known issue, and you can see details here:

https://connect.microsoft.com/SQLServer/feedback/details/265956/suppress-namespace-attributes-in-nested-select-for-xml-statements

Additional namespaces doesn't do any harm but increasing size of generated xml document.

Giorgi Nakeuri
  • 35,155
  • 8
  • 47
  • 75