-- XPathFunctions_ID-IDREF_BookPublisher_Complete_Script

-- Step 1
-- Switch to tempdb to prevent any "accidents" in your current database.
use	tempdb
go


-- Step 2
-- Drop the BookPublisher XML Schema Collection if it already exists,
-- and then re-create it.
if	(exists (
		select	*
			from	sys.xml_schema_collections
			where	(xml_schema_collections.[schema_id] = schema_id('dbo'))
				and (xml_schema_collections.name = 'BookPublisher')
		))
	begin
		drop xml schema collection	dbo.BookPublisher
	end
go

create xml schema collection	dbo.BookPublisher
as
N'<?xml version="1.0" encoding="UTF-16"?>
<xs:schema
	xmlns:xs="http://www.w3.org/2001/XMLSchema"
	targetNamespace="http://schemas.milambda.net/pubs"
	elementFormDefault="qualified"
	attributeFormDefault="qualified">
	<xs:element name="pubs">
		<xs:complexType>
			<xs:sequence>
				<xs:element name="publisherCollection">
					<xs:complexType>
						<xs:sequence>
							<xs:element name="publisher" maxOccurs="unbounded">
								<xs:complexType>
									<xs:sequence>
										<xs:element name="name" type="xs:string"/>
										<xs:element name="city" type="xs:string"/>
										<xs:element name="state" type="xs:string" nillable="true"/>
										<xs:element name="country" type="xs:string"/>
									</xs:sequence>
									<xs:attribute name="publisherId" type="xs:ID" use="required"/>
								</xs:complexType>
							</xs:element>
						</xs:sequence>
					</xs:complexType>
				</xs:element>
				<xs:element name="titleCollection">
					<xs:complexType>
						<xs:sequence>
							<xs:element name="title" maxOccurs="unbounded">
								<xs:complexType>
									<xs:sequence>
										<xs:element name="title" type="xs:string"/>
										<xs:element name="type" type="xs:string"/>
										<xs:element name="price" type="xs:decimal" nillable="true"/>
									</xs:sequence>
									<xs:attribute name="titleId" type="xs:ID" use="required"/>
									<xs:attribute name="publisherId" type="xs:IDREF" use="optional"/>
								</xs:complexType>
							</xs:element>
						</xs:sequence>
					</xs:complexType>
				</xs:element>
			</xs:sequence>
		</xs:complexType>
	</xs:element>
</xs:schema>'
go


-- Step 3
-- Assign the XML document to a variable and execute the queries.
-- The XML document is typed, making it possible to utilize the ID/IDREF XML Schema data types.
declare	@pubs	xml(dbo.BookPublisher)

set	@pubs = N'<bp:pubs xmlns:bp="http://schemas.milambda.net/pubs" xmlns:xs="http://www.w3.org/2001/XMLSchema">
	<bp:publisherCollection xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:bp="http://schemas.milambda.net/pubs" xmlns:xs="http://www.w3.org/2001/XMLSchema">
		<bp:publisher bp:publisherId="Pub_0736">
			<bp:name>New Moon Books</bp:name>
			<bp:city>Boston</bp:city>
			<bp:state>MA</bp:state>
			<bp:country>USA</bp:country>
		</bp:publisher>
		<bp:publisher bp:publisherId="Pub_1389">
			<bp:name>Algodata Infosystems</bp:name>
			<bp:city>Berkeley</bp:city>
			<bp:state>CA</bp:state>
			<bp:country>USA</bp:country>
		</bp:publisher>
	</bp:publisherCollection>
	<bp:titleCollection xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:bp="http://schemas.milambda.net/pubs" xmlns:xs="http://www.w3.org/2001/XMLSchema">
		<bp:title bp:titleId="Titl_BU1032" bp:publisherId="Pub_1389">
			<bp:title>The Busy Executive''s Database Guide</bp:title>
			<bp:type>business    </bp:type>
			<bp:price>19.99</bp:price>
		</bp:title>
		<bp:title bp:titleId="Titl_BU1111" bp:publisherId="Pub_1389">
			<bp:title>Cooking with Computers: Surreptitious Balance Sheets</bp:title>
			<bp:type>business    </bp:type>
			<bp:price>11.95</bp:price>
		</bp:title>
		<bp:title bp:titleId="Titl_BU2075" bp:publisherId="Pub_0736">
			<bp:title>You Can Combat Computer Stress!</bp:title>
			<bp:type>business    </bp:type>
			<bp:price>2.99</bp:price>
		</bp:title>
		<bp:title bp:titleId="Titl_BU7832" bp:publisherId="Pub_1389">
			<bp:title>Straight Talk About Computers</bp:title>
			<bp:type>business    </bp:type>
			<bp:price>19.99</bp:price>
		</bp:title>
	</bp:titleCollection>
</bp:pubs>'

-- The XML document:
select	@pubs as [BookPublisher]

-- Publishers whose books cost less than $11:
-- (Using the ID XPath function.)
begin
	with	xmlnamespaces
		(
		'http://www.w3.org/2001/XMLSchema' as xs
		,'http://schemas.milambda.net/pubs' as bp
		)
	select	Pubs.Publisher.query('bp:name').value('.', 'varchar(40)') as PublisherName
		from	@pubs.nodes
				('
				id(data(/bp:pubs/bp:titleCollection/bp:title[bp:price < 11]/@bp:publisherId))
				') Pubs (Publisher)
end

-- Books published by a publisher in Berkeley:
-- (Using XQuery, because the IDREF XPath function has not been implemented in SQL Server.)
begin
	with	xmlnamespaces
		(
		'http://www.w3.org/2001/XMLSchema' as xs
		,'http://schemas.milambda.net/pubs' as bp
		)
	select	Titles.Title.query('bp:title').value('.', 'varchar(80)') as Title
		from	@pubs.nodes
				('
				for $p in /bp:pubs/bp:publisherCollection/bp:publisher
					[bp:city = "Berkeley"]/@bp:publisherId
				return /bp:pubs/bp:titleCollection/bp:title[@bp:publisherId = $p]
				') Titles (Title)
end
go