CODEWITHSUNDEEP
sqlsql-serverxmlschema
WushuDrew
WushuDrew

Reputation: 188

Display XML Schema in SQL Query Result

I'm brand new to SQL and SQL Server Management Studio, so bear with me. Here's a shortened version of my query:

SELECT *
FROM [dbo].[BranchLocations] WHERE NOT (PropertyOwnerType IS NULL)
FOR XML PATH('LocationData'), ROOT('MainRoot')

This gives me exactly what I need except for the schema. I don't want to automatically generate a schema because the service to which I upload the XML data only accepts a specific schema structure, and I would prefer to control it.

My schema looks similar to this:

<xs:schema id="LocationData_ds" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
    <xs:element name="LocationData_ds" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
        <xs:complexType>
        <xs:choice minOccurs="0" maxOccurs="unbounded">
            <xs:element name="LocationData">
            <xs:complexType>
                <xs:sequence>
                <xs:element name="EntityID" type="xs:string" />
                <xs:element name="AddressLine" type="xs:string" minOccurs="0" />
                <xs:element name="Locality" type="xs:string" minOccurs="0" />
                <xs:element name="AdminDistrict" type="xs:string" minOccurs="0" />
                <xs:element name="PostalCode" type="xs:string" minOccurs="0" />
                <xs:element name="CountryRegion" type="xs:string" minOccurs="0" />
                <xs:element name="Latitude" type="xs:double" minOccurs="0" />
                <xs:element name="Longitude" type="xs:double" minOccurs="0" />
                </xs:sequence>
            </xs:complexType>
            </xs:element>
        </xs:choice>
        </xs:complexType>
        <xs:unique name="Constraint1" msdata:PrimaryKey="true">
        <xs:selector xpath=".//LocationData" />
        <xs:field xpath="EntityID" />
        </xs:unique>
    </xs:element>
</xs:schema>

My query returns this:

<MainRoot>
  <LocationData>
    <EntityID>10010000</EntityID>
    <AddressLine>1234 address<\/AddressLine>
    <Locality>Converse</Locality>
    <AdminDistrict>TX</AdminDistrict>
    <PostalCode>12345</PostalCode>
    <CountryRegion>US</CountryRegion>
  </LocationData>
  <LocationData>
    <EntityID>70390000</EntityID>
    <AddressLine>1234 address<\/AddressLine>
    <Locality>Denver</Locality>
    <AdminDistrict>CO</AdminDistrict>
    <PostalCode>12345</PostalCode>
    <CountryRegion>US</CountryRegion>
  </LocationData>
...
<MainRoot>

And what I need is this:
(and I need ti include<?xml version="1.0" encoding="utf-8" standalone="yes"?> at the top as well)

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<MainRoot>
    <xs:schema id="LocationData_ds" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
        <xs:element name="LocationData_ds" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
            <xs:complexType>
            <xs:choice minOccurs="0" maxOccurs="unbounded">
                <xs:element name="LocationData">
                <xs:complexType>
                    <xs:sequence>
                    <xs:element name="EntityID" type="xs:string" />
                    <xs:element name="AddressLine" type="xs:string" minOccurs="0" />
                    <xs:element name="Locality" type="xs:string" minOccurs="0" />
                    <xs:element name="AdminDistrict" type="xs:string" minOccurs="0" />
                    <xs:element name="PostalCode" type="xs:string" minOccurs="0" />
                    <xs:element name="CountryRegion" type="xs:string" minOccurs="0" />
                    <xs:element name="Latitude" type="xs:double" minOccurs="0" />
                    <xs:element name="Longitude" type="xs:double" minOccurs="0" />
                    </xs:sequence>
                </xs:complexType>
                </xs:element>
            </xs:choice>
            </xs:complexType>
            <xs:unique name="Constraint1" msdata:PrimaryKey="true">
            <xs:selector xpath=".//LocationData" />
            <xs:field xpath="EntityID" />
            </xs:unique>
        </xs:element>
    </xs:schema>
<LocationData>
    <EntityID>10010000</EntityID>
    <AddressLine>1234 address<\/AddressLine>
    <Locality>Converse</Locality>
    <AdminDistrict>TX</AdminDistrict>
    <PostalCode>12345</PostalCode>
    <CountryRegion>US</CountryRegion>
  </LocationData>
  <LocationData>
    <EntityID>70390000</EntityID>
    <AddressLine>1234 address<\/AddressLine>
    <Locality>Denver</Locality>
    <AdminDistrict>CO</AdminDistrict>
    <PostalCode>12345</PostalCode>
    <CountryRegion>US</CountryRegion>
  </LocationData>
...
<MainRoot>

Looking through the documentation has led me here:
https://learn.microsoft.com/en-us/sql/t-sql/xml/insert-xml-dml?view=sql-server-ver15
It isn't working, but I think I might be on the right track.

These answers only relate to generating a schema:
convert database table into XML schema file
how to create XML schema from an existing database in SQL Server 2008

Upvotes: 0

Views: 1696

Answers (2)

lptr
lptr

Reputation: 6788

declare @BranchLocations table
(
EntityId varchar(20),
AddressLine varchar(20),
Locality varchar(20),
AdminDistrict varchar(20),
PostalCode varchar(20),
CountryRegion varchar(20),
PropertyOwnerType varchar(20)
); 


insert into @BranchLocations(EntityId, AddressLine, Locality, AdminDistrict, PostalCode, CountryRegion, PropertyOwnerType)
values
('10010000', '1234 address', 'Converse', 'TX', '12345', 'US', 'x'),
('70390000', '1234 address', 'Denver', 'CO', '12345', 'US', 'x');


declare @myschema xml = N'<xs:schema id="LocationData_ds" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
    <xs:element name="LocationData_ds" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
        <xs:complexType>
        <xs:choice minOccurs="0" maxOccurs="unbounded">
            <xs:element name="LocationData">
            <xs:complexType>
                <xs:sequence>
                <xs:element name="EntityID" type="xs:string" />
                <xs:element name="AddressLine" type="xs:string" minOccurs="0" />
                <xs:element name="Locality" type="xs:string" minOccurs="0" />
                <xs:element name="AdminDistrict" type="xs:string" minOccurs="0" />
                <xs:element name="PostalCode" type="xs:string" minOccurs="0" />
                <xs:element name="CountryRegion" type="xs:string" minOccurs="0" />
                <xs:element name="Latitude" type="xs:double" minOccurs="0" />
                <xs:element name="Longitude" type="xs:double" minOccurs="0" />
                </xs:sequence>
            </xs:complexType>
            </xs:element>
        </xs:choice>
        </xs:complexType>
        <xs:unique name="Constraint1" msdata:PrimaryKey="true">
        <xs:selector xpath=".//LocationData" />
        <xs:field xpath="EntityID" />
        </xs:unique>
    </xs:element>
</xs:schema>';

declare @res nvarchar(max);
select @res = N'<?xml version="1.0" encoding="utf-8" standalone="yes"?>' + 
(
select *
from
(
    select @myschema as '*'
    union all
    select
    (
    select *
    FROM @BranchLocations 
    WHERE PropertyOwnerType is not null
    FOR XML PATH('LocationData')
    )
) as src
for xml path(''), root('Mainroot')
);


--test, show result
select 1 as tag, 0 as parent, 
        @res+'</foo>' as 'test!1!!xmltext'
for xml explicit;


--or
select @res = null;
declare @myxml xml = (  select *
    FROM @BranchLocations 
    WHERE PropertyOwnerType is not null
    and 1=2
    FOR XML PATH('LocationData'));

--null result when empty resultset from table   
select @res = 
N'<?xml version="1.0" encoding="utf-8" standalone="yes"?>'
+(select @myschema,@myxml for xml path(''), root('Mainroot'))
where @myxml is not null;

select @res;

Upvotes: 2

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22187

What you are looking for is called an Inline XSD Schema. Here is BOL link: Generate an Inline XSD Schema

Unfortunately, it is very limited: "...You can specify XMLSCHEMA only in RAW and AUTO mode, not in EXPLICIT mode...". Same with the PATH mode.

Additional limitation is that SQL Server keeps XML as UTF-16, and strips an XML prolog from any XML data type internally.

To achieve what you need everything needs to be done manually, i.e. stitch together a prolog, XML Schema, and actual XML.

Upvotes: 0

Related Questions