~ubuntu-branches/ubuntu/oneiric/postgresql-9.1/oneiric-security

CREATE TABLE testxmlschema.test2 (z int, y varchar(500), x char(6), w numeric(9,2), v smallint, u bigint, t real, s time, r timestamp, q date, p xml, o testxmldomain, n bool, m bytea, aaa text);

ALTER TABLE testxmlschema.test2 DROP COLUMN aaa;

INSERT INTO testxmlschema.test2 VALUES (55, 'abc', 'def', 98.6, 2, 999, 0, '21:07', '2009-06-08 21:07:30', '2009-06-08', NULL, 'ABC', true, 'XYZ');

SELECT table_to_xml('testxmlschema.test1', false, false, '');

table_to_xml

---------------------------------------------------------------

<test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+

<row> +

<a>1</a> +

one +

</row> +

<row> +

<a>2</a> +

two +

</row> +

<row> +

<a>-1</a> +

</row> +

</test1> +

(1 row)

SELECT table_to_xml('testxmlschema.test1', true, false, 'foo');

table_to_xml

---------------------------------------------------------------------------

<test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="foo">+

<row> +

<a>1</a> +

one +

</row> +

<row> +

<a>2</a> +

two +

</row> +

<row> +

<a>-1</a> +

+

</row> +

</test1> +

(1 row)

SELECT table_to_xml('testxmlschema.test1', false, true, '');

table_to_xml

---------------------------------------------------------------

<test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+

<a>1</a> +

one +

</test1> +

<test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+

<a>2</a> +

two +

</test1> +

<test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+

<a>-1</a> +

</test1> +

(1 row)

SELECT table_to_xml('testxmlschema.test1', true, true, '');

table_to_xml

---------------------------------------------------------------

<test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+

<a>1</a> +

one +

</test1> +

<test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+

<a>2</a> +

two +

</test1> +

<test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+

<a>-1</a> +

+

</test1> +

100

(1 row)

101

102

SELECT table_to_xml('testxmlschema.test2', false, false, '');

103

table_to_xml

104

---------------------------------------------------------------

105

<test2 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+

106

107

<row> +

108

<z>55</z> +

109

<y>abc</y> +

110

<x>def </x> +

111

<w>98.60</w> +

112

<v>2</v> +

113

999 +

114

<t>0</t> +

115

<s>21:07:00</s> +

116

<r>2009-06-08T21:07:30</r> +

117

<q>2009-06-08</q> +

118

<o>ABC</o> +

119

<n>true</n> +

120

<m>WFla</m> +

121

</row> +

122

123

</test2> +

124

125

(1 row)

126

127

SELECT table_to_xmlschema('testxmlschema.test1', false, false, '');

128

table_to_xmlschema

129

-----------------------------------------------------------------------------------------------------------------

130

<xsd:schema +

131

xmlns:xsd="http://www.w3.org/2001/XMLSchema"> +

132

133

<xsd:simpleType name="INTEGER"> +

134

<xsd:restriction base="xsd:int"> +

135

<xsd:maxInclusive value="2147483647"/> +

136

<xsd:minInclusive value="-2147483648"/> +

137

</xsd:restriction> +

138

</xsd:simpleType> +

139

140

<xsd:simpleType name="UDT.regression.pg_catalog.text"> +

141

<xsd:restriction base="xsd:string"> +

142

</xsd:restriction> +

143

</xsd:simpleType> +

144

145

<xsd:complexType name="RowType.regression.testxmlschema.test1"> +

146

<xsd:sequence> +

147

<xsd:element name="a" type="INTEGER" minOccurs="0"></xsd:element> +

148

<xsd:element name="b" type="UDT.regression.pg_catalog.text" minOccurs="0"></xsd:element> +

149

</xsd:sequence> +

150

</xsd:complexType> +

151

152

<xsd:complexType name="TableType.regression.testxmlschema.test1"> +

153

<xsd:sequence> +

154

<xsd:element name="row" type="RowType.regression.testxmlschema.test1" minOccurs="0" maxOccurs="unbounded"/>+

155

</xsd:sequence> +

156

</xsd:complexType> +

157

158

<xsd:element name="test1" type="TableType.regression.testxmlschema.test1"/> +

159

160

</xsd:schema>

161

(1 row)

162

163

SELECT table_to_xmlschema('testxmlschema.test1', true, false, '');

164

table_to_xmlschema

165

-----------------------------------------------------------------------------------------------------------------

166

<xsd:schema +

167

xmlns:xsd="http://www.w3.org/2001/XMLSchema"> +

168

169

<xsd:simpleType name="INTEGER"> +

170

<xsd:restriction base="xsd:int"> +

171

<xsd:maxInclusive value="2147483647"/> +

172

<xsd:minInclusive value="-2147483648"/> +

173

</xsd:restriction> +

174

</xsd:simpleType> +

175

176

<xsd:simpleType name="UDT.regression.pg_catalog.text"> +

177

<xsd:restriction base="xsd:string"> +

178

</xsd:restriction> +

179

</xsd:simpleType> +

180

181

<xsd:complexType name="RowType.regression.testxmlschema.test1"> +

182

<xsd:sequence> +

183

<xsd:element name="a" type="INTEGER" nillable="true"></xsd:element> +

184

<xsd:element name="b" type="UDT.regression.pg_catalog.text" nillable="true"></xsd:element> +

185

</xsd:sequence> +

186

</xsd:complexType> +

187

188

<xsd:complexType name="TableType.regression.testxmlschema.test1"> +

189

<xsd:sequence> +

190

<xsd:element name="row" type="RowType.regression.testxmlschema.test1" minOccurs="0" maxOccurs="unbounded"/>+

191

</xsd:sequence> +

192

</xsd:complexType> +

193

194

<xsd:element name="test1" type="TableType.regression.testxmlschema.test1"/> +

195

196

</xsd:schema>

197

(1 row)

198

199

SELECT table_to_xmlschema('testxmlschema.test1', false, true, 'foo');

200

table_to_xmlschema

201

----------------------------------------------------------------------------------------------

202

<xsd:schema +

203

xmlns:xsd="http://www.w3.org/2001/XMLSchema" +

204

targetNamespace="foo" +

205

elementFormDefault="qualified"> +

206

207

<xsd:simpleType name="INTEGER"> +

208

<xsd:restriction base="xsd:int"> +

209

<xsd:maxInclusive value="2147483647"/> +

210

<xsd:minInclusive value="-2147483648"/> +

211

</xsd:restriction> +

212

</xsd:simpleType> +

213

214

<xsd:simpleType name="UDT.regression.pg_catalog.text"> +

215

<xsd:restriction base="xsd:string"> +

216

</xsd:restriction> +

217

</xsd:simpleType> +

218

219

<xsd:complexType name="RowType.regression.testxmlschema.test1"> +

220

<xsd:sequence> +

221

<xsd:element name="a" type="INTEGER" minOccurs="0"></xsd:element> +

222

<xsd:element name="b" type="UDT.regression.pg_catalog.text" minOccurs="0"></xsd:element>+

223

</xsd:sequence> +

224

</xsd:complexType> +

225

226

<xsd:element name="test1" type="RowType.regression.testxmlschema.test1"/> +

227

228

</xsd:schema>

229

(1 row)

230

231

SELECT table_to_xmlschema('testxmlschema.test1', true, true, '');

232

table_to_xmlschema

233

------------------------------------------------------------------------------------------------

234

<xsd:schema +

235

xmlns:xsd="http://www.w3.org/2001/XMLSchema"> +

236

237

<xsd:simpleType name="INTEGER"> +

238

<xsd:restriction base="xsd:int"> +

239

<xsd:maxInclusive value="2147483647"/> +

240

<xsd:minInclusive value="-2147483648"/> +

241

</xsd:restriction> +

242

</xsd:simpleType> +

243

244

<xsd:simpleType name="UDT.regression.pg_catalog.text"> +

245

<xsd:restriction base="xsd:string"> +

246

</xsd:restriction> +

247

</xsd:simpleType> +

248

249

<xsd:complexType name="RowType.regression.testxmlschema.test1"> +

250

<xsd:sequence> +

251

<xsd:element name="a" type="INTEGER" nillable="true"></xsd:element> +

252

<xsd:element name="b" type="UDT.regression.pg_catalog.text" nillable="true"></xsd:element>+

253

</xsd:sequence> +

254

</xsd:complexType> +

255

256

<xsd:element name="test1" type="RowType.regression.testxmlschema.test1"/> +

257

258

</xsd:schema>

259

(1 row)

260

261

SELECT table_to_xmlschema('testxmlschema.test2', false, false, '');

262

table_to_xmlschema

263

-----------------------------------------------------------------------------------------------------------------

264

<xsd:schema +

265

xmlns:xsd="http://www.w3.org/2001/XMLSchema"> +

266

267

<xsd:simpleType name="INTEGER"> +

268

<xsd:restriction base="xsd:int"> +

269

<xsd:maxInclusive value="2147483647"/> +

270

<xsd:minInclusive value="-2147483648"/> +

271

</xsd:restriction> +

272

</xsd:simpleType> +

273

274

<xsd:simpleType name="VARCHAR"> +

275

<xsd:restriction base="xsd:string"> +

276

</xsd:restriction> +

277

</xsd:simpleType> +

278

279

<xsd:simpleType name="CHAR"> +

280

<xsd:restriction base="xsd:string"> +

281

</xsd:restriction> +

282

</xsd:simpleType> +

283

284

<xsd:simpleType name="NUMERIC"> +

285

</xsd:simpleType> +

286

287

<xsd:simpleType name="SMALLINT"> +

288

<xsd:restriction base="xsd:short"> +

289

<xsd:maxInclusive value="32767"/> +

290

<xsd:minInclusive value="-32768"/> +

291

</xsd:restriction> +

292

</xsd:simpleType> +

293

294

<xsd:simpleType name="BIGINT"> +

295

<xsd:restriction base="xsd:long"> +

296

<xsd:maxInclusive value="9223372036854775807"/> +

297

<xsd:minInclusive value="-9223372036854775808"/> +

298

</xsd:restriction> +

299

</xsd:simpleType> +

300

301

<xsd:simpleType name="REAL"> +

302

<xsd:restriction base="xsd:float"></xsd:restriction> +

303

</xsd:simpleType> +

304

305

<xsd:simpleType name="TIME"> +

306

<xsd:restriction base="xsd:time"> +

307

<xsd:pattern value="\p{Nd}{2}:\p{Nd}{2}:\p{Nd}{2}(.\p{Nd}+)?"/> +

308

</xsd:restriction> +

309

</xsd:simpleType> +

310

311

<xsd:simpleType name="TIMESTAMP"> +

312

<xsd:restriction base="xsd:dateTime"> +

313

<xsd:pattern value="\p{Nd}{4}-\p{Nd}{2}-\p{Nd}{2}T\p{Nd}{2}:\p{Nd}{2}:\p{Nd}{2}(.\p{Nd}+)?"/> +

314

</xsd:restriction> +

315

</xsd:simpleType> +

316

317

<xsd:simpleType name="DATE"> +

318

<xsd:restriction base="xsd:date"> +

319

<xsd:pattern value="\p{Nd}{4}-\p{Nd}{2}-\p{Nd}{2}"/> +

320

</xsd:restriction> +

321

</xsd:simpleType> +

322

323

<xsd:complexType mixed="true"> +

324

<xsd:sequence> +

325

<xsd:any name="element" minOccurs="0" maxOccurs="unbounded" processContents="skip"/> +

326

</xsd:sequence> +

327

</xsd:complexType> +

328

329

<xsd:simpleType name="Domain.regression.public.testxmldomain"> +

330

<xsd:restriction base="VARCHAR"/> +

331

</xsd:simpleType> +

332

333

<xsd:simpleType name="BOOLEAN"> +

334

<xsd:restriction base="xsd:boolean"></xsd:restriction> +

335

</xsd:simpleType> +

336

337

<xsd:simpleType name="UDT.regression.pg_catalog.bytea"> +

338

<xsd:restriction base="xsd:base64Binary"> +

339

</xsd:restriction> +

340

</xsd:simpleType> +

341

342

<xsd:complexType name="RowType.regression.testxmlschema.test2"> +

343

<xsd:sequence> +

344

<xsd:element name="z" type="INTEGER" minOccurs="0"></xsd:element> +

345

<xsd:element name="y" type="VARCHAR" minOccurs="0"></xsd:element> +

346

<xsd:element name="x" type="CHAR" minOccurs="0"></xsd:element> +

347

<xsd:element name="w" type="NUMERIC" minOccurs="0"></xsd:element> +

348

<xsd:element name="v" type="SMALLINT" minOccurs="0"></xsd:element> +

349

<xsd:element name="u" type="BIGINT" minOccurs="0"></xsd:element> +

350

<xsd:element name="t" type="REAL" minOccurs="0"></xsd:element> +

351

<xsd:element name="s" type="TIME" minOccurs="0"></xsd:element> +

352

<xsd:element name="r" type="TIMESTAMP" minOccurs="0"></xsd:element> +

353

<xsd:element name="q" type="DATE" minOccurs="0"></xsd:element> +

354

<xsd:element name="p" type="XML" minOccurs="0"></xsd:element> +

355

<xsd:element name="o" type="Domain.regression.public.testxmldomain" minOccurs="0"></xsd:element> +

356

<xsd:element name="n" type="BOOLEAN" minOccurs="0"></xsd:element> +

357

<xsd:element name="m" type="UDT.regression.pg_catalog.bytea" minOccurs="0"></xsd:element> +

358

</xsd:sequence> +

359

</xsd:complexType> +

360

361

<xsd:complexType name="TableType.regression.testxmlschema.test2"> +

362

<xsd:sequence> +

363

<xsd:element name="row" type="RowType.regression.testxmlschema.test2" minOccurs="0" maxOccurs="unbounded"/>+

364

</xsd:sequence> +

365

</xsd:complexType> +

366

367

<xsd:element name="test2" type="TableType.regression.testxmlschema.test2"/> +

368

369

</xsd:schema>

370

(1 row)

371

372

SELECT table_to_xml_and_xmlschema('testxmlschema.test1', false, false, '');

373

table_to_xml_and_xmlschema

374

-----------------------------------------------------------------------------------------------------------------

375

<test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="#"> +

376

377

<xsd:schema +

378

xmlns:xsd="http://www.w3.org/2001/XMLSchema"> +

379

380

<xsd:simpleType name="INTEGER"> +

381

<xsd:restriction base="xsd:int"> +

382

<xsd:maxInclusive value="2147483647"/> +

383

<xsd:minInclusive value="-2147483648"/> +

384

</xsd:restriction> +

385

</xsd:simpleType> +

386

387

<xsd:simpleType name="UDT.regression.pg_catalog.text"> +

388

<xsd:restriction base="xsd:string"> +

389

</xsd:restriction> +

390

</xsd:simpleType> +

391

392

<xsd:complexType name="RowType.regression.testxmlschema.test1"> +

393

<xsd:sequence> +

394

<xsd:element name="a" type="INTEGER" minOccurs="0"></xsd:element> +

395

<xsd:element name="b" type="UDT.regression.pg_catalog.text" minOccurs="0"></xsd:element> +

396

</xsd:sequence> +

397

</xsd:complexType> +

398

399

<xsd:complexType name="TableType.regression.testxmlschema.test1"> +

400

<xsd:sequence> +

401

<xsd:element name="row" type="RowType.regression.testxmlschema.test1" minOccurs="0" maxOccurs="unbounded"/>+

402

</xsd:sequence> +

403

</xsd:complexType> +

404

405

<xsd:element name="test1" type="TableType.regression.testxmlschema.test1"/> +

406

407

</xsd:schema> +

408

409

<row> +

410

<a>1</a> +

411

one +

412

</row> +

413

414

<row> +

415

<a>2</a> +

416

two +

417

</row> +

418

419

<row> +

420

<a>-1</a> +

421

</row> +

422

423

</test1> +

424

425

(1 row)

426

427

SELECT table_to_xml_and_xmlschema('testxmlschema.test1', true, false, '');

428

table_to_xml_and_xmlschema

429

-----------------------------------------------------------------------------------------------------------------

430

<test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="#"> +

431

432

<xsd:schema +

433

xmlns:xsd="http://www.w3.org/2001/XMLSchema"> +

434

435

<xsd:simpleType name="INTEGER"> +

436

<xsd:restriction base="xsd:int"> +

437

<xsd:maxInclusive value="2147483647"/> +

438

<xsd:minInclusive value="-2147483648"/> +

439

</xsd:restriction> +

440

</xsd:simpleType> +

441

442

<xsd:simpleType name="UDT.regression.pg_catalog.text"> +

443

<xsd:restriction base="xsd:string"> +

444

</xsd:restriction> +

445

</xsd:simpleType> +

446

447

<xsd:complexType name="RowType.regression.testxmlschema.test1"> +

448

<xsd:sequence> +

449

<xsd:element name="a" type="INTEGER" nillable="true"></xsd:element> +

450

<xsd:element name="b" type="UDT.regression.pg_catalog.text" nillable="true"></xsd:element> +

451

</xsd:sequence> +

452

</xsd:complexType> +

453

454

<xsd:complexType name="TableType.regression.testxmlschema.test1"> +

455

<xsd:sequence> +

456

<xsd:element name="row" type="RowType.regression.testxmlschema.test1" minOccurs="0" maxOccurs="unbounded"/>+

457

</xsd:sequence> +

458

</xsd:complexType> +

459

460

<xsd:element name="test1" type="TableType.regression.testxmlschema.test1"/> +

461

462

</xsd:schema> +

463

464

<row> +

465

<a>1</a> +

466

one +

467

</row> +

468

469

<row> +

470

<a>2</a> +

471

two +

472

</row> +

473

474

<row> +

475

<a>-1</a> +

476

+

477

</row> +

478

479

</test1> +

480

481

(1 row)

482

483

SELECT table_to_xml_and_xmlschema('testxmlschema.test1', false, true, '');

484

table_to_xml_and_xmlschema

485

----------------------------------------------------------------------------------------------

486

<xsd:schema +

487

xmlns:xsd="http://www.w3.org/2001/XMLSchema"> +

488

489

<xsd:simpleType name="INTEGER"> +

490

<xsd:restriction base="xsd:int"> +

491

<xsd:maxInclusive value="2147483647"/> +

492

<xsd:minInclusive value="-2147483648"/> +

493

</xsd:restriction> +

494

</xsd:simpleType> +

495

496

<xsd:simpleType name="UDT.regression.pg_catalog.text"> +

497

<xsd:restriction base="xsd:string"> +

498

</xsd:restriction> +

499

</xsd:simpleType> +

500

501

<xsd:complexType name="RowType.regression.testxmlschema.test1"> +

502

<xsd:sequence> +

503

<xsd:element name="a" type="INTEGER" minOccurs="0"></xsd:element> +

504

<xsd:element name="b" type="UDT.regression.pg_catalog.text" minOccurs="0"></xsd:element>+

505

</xsd:sequence> +

506

</xsd:complexType> +

507

508

<xsd:element name="test1" type="RowType.regression.testxmlschema.test1"/> +

509

510

</xsd:schema> +

511

512

<test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> +

513

514

<a>1</a> +

515

one +

516

</test1> +

517

518

<test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> +

519

520

<a>2</a> +

521

two +

522

</test1> +

523

524

<test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> +

525

526

<a>-1</a> +

527

</test1> +

528

529

530

(1 row)

531

532

SELECT table_to_xml_and_xmlschema('testxmlschema.test1', true, true, 'foo');

533

table_to_xml_and_xmlschema

534

------------------------------------------------------------------------------------------------

535

<xsd:schema +

536

xmlns:xsd="http://www.w3.org/2001/XMLSchema" +

537

targetNamespace="foo" +

538

elementFormDefault="qualified"> +

539

540

<xsd:simpleType name="INTEGER"> +

541

<xsd:restriction base="xsd:int"> +

542

<xsd:maxInclusive value="2147483647"/> +

543

<xsd:minInclusive value="-2147483648"/> +

544

</xsd:restriction> +

545

</xsd:simpleType> +

546

547

<xsd:simpleType name="UDT.regression.pg_catalog.text"> +

548

<xsd:restriction base="xsd:string"> +

549

</xsd:restriction> +

550

</xsd:simpleType> +

551

552

<xsd:complexType name="RowType.regression.testxmlschema.test1"> +

553

<xsd:sequence> +

554

<xsd:element name="a" type="INTEGER" nillable="true"></xsd:element> +

555

<xsd:element name="b" type="UDT.regression.pg_catalog.text" nillable="true"></xsd:element>+

556

</xsd:sequence> +

557

</xsd:complexType> +

558

559

<xsd:element name="test1" type="RowType.regression.testxmlschema.test1"/> +

560

561

</xsd:schema> +

562

563

<test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="foo"> +

564

565

<a>1</a> +

566

one +

567

</test1> +

568

569

<test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="foo"> +

570

571

<a>2</a> +

572

two +

573

</test1> +

574

575

<test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="foo"> +

576

577

<a>-1</a> +

578

+

579

</test1> +

580

581

582

(1 row)

583

584

SELECT query_to_xml('SELECT * FROM testxmlschema.test1', false, false, '');

585

query_to_xml

586

---------------------------------------------------------------

587

<table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+

588

589

<row> +

590

<a>1</a> +

591

one +

592

</row> +

593

594

<row> +

595

<a>2</a> +

596

two +

597

</row> +

598

599

<row> +

600

<a>-1</a> +

601

</row> +

602

603

</table> +

604

605

(1 row)

606

607

SELECT query_to_xmlschema('SELECT * FROM testxmlschema.test1', false, false, '');

608

query_to_xmlschema

609

----------------------------------------------------------------------------------------------

610

<xsd:schema +

611

xmlns:xsd="http://www.w3.org/2001/XMLSchema"> +

612

613

<xsd:simpleType name="INTEGER"> +

614

<xsd:restriction base="xsd:int"> +

615

<xsd:maxInclusive value="2147483647"/> +

616

<xsd:minInclusive value="-2147483648"/> +

617

</xsd:restriction> +

618

</xsd:simpleType> +

619

620

<xsd:simpleType name="UDT.regression.pg_catalog.text"> +

621

<xsd:restriction base="xsd:string"> +

622

</xsd:restriction> +

623

</xsd:simpleType> +

624

625

<xsd:complexType name="RowType"> +

626

<xsd:sequence> +

627

<xsd:element name="a" type="INTEGER" minOccurs="0"></xsd:element> +

628

<xsd:element name="b" type="UDT.regression.pg_catalog.text" minOccurs="0"></xsd:element>+

629

</xsd:sequence> +

630

</xsd:complexType> +

631

632

<xsd:complexType name="TableType"> +

633

<xsd:sequence> +

634

<xsd:element name="row" type="RowType" minOccurs="0" maxOccurs="unbounded"/> +

635

</xsd:sequence> +

636

</xsd:complexType> +

637

638

<xsd:element name="table" type="TableType"/> +

639

640

</xsd:schema>

641

(1 row)

642

643

SELECT query_to_xml_and_xmlschema('SELECT * FROM testxmlschema.test1', true, true, '');

644

query_to_xml_and_xmlschema

645

------------------------------------------------------------------------------------------------

646

<xsd:schema +

647

xmlns:xsd="http://www.w3.org/2001/XMLSchema"> +

648

649

<xsd:simpleType name="INTEGER"> +

650

<xsd:restriction base="xsd:int"> +

651

<xsd:maxInclusive value="2147483647"/> +

652

<xsd:minInclusive value="-2147483648"/> +

653

</xsd:restriction> +

654

</xsd:simpleType> +

655

656

<xsd:simpleType name="UDT.regression.pg_catalog.text"> +

657

<xsd:restriction base="xsd:string"> +

658

</xsd:restriction> +

659

</xsd:simpleType> +

660

661

<xsd:complexType name="RowType"> +

662

<xsd:sequence> +

663

<xsd:element name="a" type="INTEGER" nillable="true"></xsd:element> +

664

<xsd:element name="b" type="UDT.regression.pg_catalog.text" nillable="true"></xsd:element>+

665

</xsd:sequence> +

666

</xsd:complexType> +

667

668

<xsd:element name="row" type="RowType"/> +

669

670

</xsd:schema> +

671

672

<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> +

673

674

<a>1</a> +

675

one +

676

</row> +

677

678

<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> +

679

680

<a>2</a> +

681

two +

682

</row> +

683

684

<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> +

685

686

<a>-1</a> +

687

+

688

</row> +

689

690

691

(1 row)

692

693

DECLARE xc CURSOR WITH HOLD FOR SELECT * FROM testxmlschema.test1 ORDER BY 1, 2;

694

SELECT cursor_to_xml('xc'::refcursor, 5, false, true, '');

695

cursor_to_xml

696

-------------------------------------------------------------

697

<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+

698

699

<a>-1</a> +

700

</row> +

701

702

<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+

703

704

<a>1</a> +

705

one +

706

</row> +

707

708

<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+

709

710

<a>2</a> +

711

two +

712

</row> +

713

714

715

(1 row)

716

717

MOVE FIRST IN xc;

718

SELECT cursor_to_xml('xc'::refcursor, 5, true, false, '');

719

cursor_to_xml

720

---------------

721

<row> +

722

<a>1</a> +

723

one +

724

</row> +

725

726

<row> +

727

<a>2</a> +

728

two +

729

</row> +

730

731

732

(1 row)

733

734

SELECT cursor_to_xmlschema('xc'::refcursor, true, false, '');

735

cursor_to_xmlschema

736

------------------------------------------------------------------------------------------------

737

<xsd:schema +

738

xmlns:xsd="http://www.w3.org/2001/XMLSchema"> +

739

740

<xsd:simpleType name="INTEGER"> +

741

<xsd:restriction base="xsd:int"> +

742

<xsd:maxInclusive value="2147483647"/> +

743

<xsd:minInclusive value="-2147483648"/> +

744

</xsd:restriction> +

745

</xsd:simpleType> +

746

747

<xsd:simpleType name="UDT.regression.pg_catalog.text"> +

748

<xsd:restriction base="xsd:string"> +

749

</xsd:restriction> +

750

</xsd:simpleType> +

751

752

<xsd:complexType name="RowType"> +

753

<xsd:sequence> +

754

<xsd:element name="a" type="INTEGER" nillable="true"></xsd:element> +

755

<xsd:element name="b" type="UDT.regression.pg_catalog.text" nillable="true"></xsd:element>+

756

</xsd:sequence> +

757

</xsd:complexType> +

758

759

<xsd:complexType name="TableType"> +

760

<xsd:sequence> +

761

<xsd:element name="row" type="RowType" minOccurs="0" maxOccurs="unbounded"/> +

762

</xsd:sequence> +

763

</xsd:complexType> +

764

765

<xsd:element name="table" type="TableType"/> +

766

767

</xsd:schema>

768

(1 row)

769

770

SELECT schema_to_xml('testxmlschema', false, true, '');

771

schema_to_xml

772

-----------------------------------------------------------------------

773

<testxmlschema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+

774

775

<test1> +

776

777

<a>1</a> +

778

one +

779

</test1> +

780

781

<test1> +

782

783

<a>2</a> +

784

two +

785

</test1> +

786

787

<test1> +

788

789

<a>-1</a> +

790

</test1> +

791

792

793

<test2> +

794

795

<z>55</z> +

796

<y>abc</y> +

797

<x>def </x> +

798

<w>98.60</w> +

799

<v>2</v> +

800

999 +

801

<t>0</t> +

802

<s>21:07:00</s> +

803

<r>2009-06-08T21:07:30</r> +

804

<q>2009-06-08</q> +

805

<o>ABC</o> +

806

<n>true</n> +

807

<m>WFla</m> +

808

</test2> +

809

810

811

</testxmlschema> +

812

813

(1 row)

814

815

SELECT schema_to_xml('testxmlschema', true, false, '');

816

schema_to_xml

817

-----------------------------------------------------------------------

818

<testxmlschema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+

819

820

<test1> +

821

822

<row> +

823

<a>1</a> +

824

one +

825

</row> +

826

827

<row> +

828

<a>2</a> +

829

two +

830

</row> +

831

832

<row> +

833

<a>-1</a> +

834

+

835

</row> +

836

837

</test1> +

838

839

<test2> +

840

841

<row> +

842

<z>55</z> +

843

<y>abc</y> +

844

<x>def </x> +

845

<w>98.60</w> +

846

<v>2</v> +

847

999 +

848

<t>0</t> +

849

<s>21:07:00</s> +

850

<r>2009-06-08T21:07:30</r> +

851

<q>2009-06-08</q> +

852

+

853

<o>ABC</o> +

854

<n>true</n> +

855

<m>WFla</m> +

856

</row> +

857

858

</test2> +

859

860

</testxmlschema> +

861

862

(1 row)

863

864

SELECT schema_to_xmlschema('testxmlschema', false, true, '');

865

schema_to_xmlschema

866

-------------------------------------------------------------------------------------------------------------------

867

<xsd:schema +

868

xmlns:xsd="http://www.w3.org/2001/XMLSchema"> +

869

870

<xsd:simpleType name="INTEGER"> +

871

<xsd:restriction base="xsd:int"> +

872

<xsd:maxInclusive value="2147483647"/> +

873

<xsd:minInclusive value="-2147483648"/> +

874

</xsd:restriction> +

875

</xsd:simpleType> +

876

877

<xsd:simpleType name="UDT.regression.pg_catalog.text"> +

878

<xsd:restriction base="xsd:string"> +

879

</xsd:restriction> +

880

</xsd:simpleType> +

881

882

<xsd:simpleType name="VARCHAR"> +

883

<xsd:restriction base="xsd:string"> +

884

</xsd:restriction> +

885

</xsd:simpleType> +

886

887

<xsd:simpleType name="CHAR"> +

888

<xsd:restriction base="xsd:string"> +

889

</xsd:restriction> +

890

</xsd:simpleType> +

891

892

<xsd:simpleType name="NUMERIC"> +

893

</xsd:simpleType> +

894

895

<xsd:simpleType name="SMALLINT"> +

896

<xsd:restriction base="xsd:short"> +

897

<xsd:maxInclusive value="32767"/> +

898

<xsd:minInclusive value="-32768"/> +

899

</xsd:restriction> +

900

</xsd:simpleType> +

901

902

<xsd:simpleType name="BIGINT"> +

903

<xsd:restriction base="xsd:long"> +

904

<xsd:maxInclusive value="9223372036854775807"/> +

905

<xsd:minInclusive value="-9223372036854775808"/> +

906

</xsd:restriction> +

907

</xsd:simpleType> +

908

909

<xsd:simpleType name="REAL"> +

910

<xsd:restriction base="xsd:float"></xsd:restriction> +

911

</xsd:simpleType> +

912

913

<xsd:simpleType name="TIME"> +

914

<xsd:restriction base="xsd:time"> +

915

<xsd:pattern value="\p{Nd}{2}:\p{Nd}{2}:\p{Nd}{2}(.\p{Nd}+)?"/> +

916

</xsd:restriction> +

917

</xsd:simpleType> +

918

919

<xsd:simpleType name="TIMESTAMP"> +

920

<xsd:restriction base="xsd:dateTime"> +

921

<xsd:pattern value="\p{Nd}{4}-\p{Nd}{2}-\p{Nd}{2}T\p{Nd}{2}:\p{Nd}{2}:\p{Nd}{2}(.\p{Nd}+)?"/> +

922

</xsd:restriction> +

923

</xsd:simpleType> +

924

925

<xsd:simpleType name="DATE"> +

926

<xsd:restriction base="xsd:date"> +

927

<xsd:pattern value="\p{Nd}{4}-\p{Nd}{2}-\p{Nd}{2}"/> +

928

</xsd:restriction> +

929

</xsd:simpleType> +

930

931

<xsd:complexType mixed="true"> +

932

<xsd:sequence> +

933

<xsd:any name="element" minOccurs="0" maxOccurs="unbounded" processContents="skip"/> +

934

</xsd:sequence> +

935

</xsd:complexType> +

936

937

<xsd:simpleType name="Domain.regression.public.testxmldomain"> +

938

<xsd:restriction base="VARCHAR"/> +

939

</xsd:simpleType> +

940

941

<xsd:simpleType name="BOOLEAN"> +

942

<xsd:restriction base="xsd:boolean"></xsd:restriction> +

943

</xsd:simpleType> +

944

945

<xsd:simpleType name="UDT.regression.pg_catalog.bytea"> +

946

<xsd:restriction base="xsd:base64Binary"> +

947

</xsd:restriction> +

948

</xsd:simpleType> +

949

950

<xsd:complexType name="SchemaType.regression.testxmlschema"> +

951

<xsd:sequence> +

952

<xsd:element name="test1" type="RowType.regression.testxmlschema.test1" minOccurs="0" maxOccurs="unbounded"/>+

953

<xsd:element name="test2" type="RowType.regression.testxmlschema.test2" minOccurs="0" maxOccurs="unbounded"/>+

954

</xsd:sequence> +

955

</xsd:complexType> +

956

957

<xsd:element name="testxmlschema" type="SchemaType.regression.testxmlschema"/> +

958

959

</xsd:schema>

960

(1 row)

961

962

SELECT schema_to_xmlschema('testxmlschema', true, false, '');

963

schema_to_xmlschema

964

---------------------------------------------------------------------------------------------------

965

<xsd:schema +

966

xmlns:xsd="http://www.w3.org/2001/XMLSchema"> +

967

968

<xsd:simpleType name="INTEGER"> +

969

<xsd:restriction base="xsd:int"> +

970

<xsd:maxInclusive value="2147483647"/> +

971

<xsd:minInclusive value="-2147483648"/> +

972

</xsd:restriction> +

973

</xsd:simpleType> +

974

975

<xsd:simpleType name="UDT.regression.pg_catalog.text"> +

976

<xsd:restriction base="xsd:string"> +

977

</xsd:restriction> +

978

</xsd:simpleType> +

979

980

<xsd:simpleType name="VARCHAR"> +

981

<xsd:restriction base="xsd:string"> +

982

</xsd:restriction> +

983

</xsd:simpleType> +

984

985

<xsd:simpleType name="CHAR"> +

986

<xsd:restriction base="xsd:string"> +

987

</xsd:restriction> +

988

</xsd:simpleType> +

989

990

<xsd:simpleType name="NUMERIC"> +

991

</xsd:simpleType> +

992

993

<xsd:simpleType name="SMALLINT"> +

994

<xsd:restriction base="xsd:short"> +

995

<xsd:maxInclusive value="32767"/> +

996

<xsd:minInclusive value="-32768"/> +

997

</xsd:restriction> +

998

</xsd:simpleType> +

999

1000

<xsd:simpleType name="BIGINT"> +

1001

<xsd:restriction base="xsd:long"> +

1002

<xsd:maxInclusive value="9223372036854775807"/> +

1003

<xsd:minInclusive value="-9223372036854775808"/> +

1004

</xsd:restriction> +

1005

</xsd:simpleType> +

1006

1007

<xsd:simpleType name="REAL"> +

1008

<xsd:restriction base="xsd:float"></xsd:restriction> +

1009

</xsd:simpleType> +

1010

1011

<xsd:simpleType name="TIME"> +

1012

<xsd:restriction base="xsd:time"> +

1013

<xsd:pattern value="\p{Nd}{2}:\p{Nd}{2}:\p{Nd}{2}(.\p{Nd}+)?"/> +

1014

</xsd:restriction> +

1015

</xsd:simpleType> +

1016

1017

<xsd:simpleType name="TIMESTAMP"> +

1018

<xsd:restriction base="xsd:dateTime"> +

1019

<xsd:pattern value="\p{Nd}{4}-\p{Nd}{2}-\p{Nd}{2}T\p{Nd}{2}:\p{Nd}{2}:\p{Nd}{2}(.\p{Nd}+)?"/>+

1020

</xsd:restriction> +

1021

</xsd:simpleType> +

1022

1023

<xsd:simpleType name="DATE"> +

1024

<xsd:restriction base="xsd:date"> +

1025

<xsd:pattern value="\p{Nd}{4}-\p{Nd}{2}-\p{Nd}{2}"/> +

1026

</xsd:restriction> +

1027

</xsd:simpleType> +

1028

1029

<xsd:complexType mixed="true"> +

1030

<xsd:sequence> +

1031

<xsd:any name="element" minOccurs="0" maxOccurs="unbounded" processContents="skip"/> +

1032

</xsd:sequence> +

1033

</xsd:complexType> +

1034

1035

<xsd:simpleType name="Domain.regression.public.testxmldomain"> +

1036

<xsd:restriction base="VARCHAR"/> +

1037

</xsd:simpleType> +

1038

1039

<xsd:simpleType name="BOOLEAN"> +

1040

<xsd:restriction base="xsd:boolean"></xsd:restriction> +

1041

</xsd:simpleType> +

1042

1043

<xsd:simpleType name="UDT.regression.pg_catalog.bytea"> +

1044

<xsd:restriction base="xsd:base64Binary"> +

1045

</xsd:restriction> +

1046

</xsd:simpleType> +

1047

1048

<xsd:complexType name="SchemaType.regression.testxmlschema"> +

1049

<xsd:all> +

1050

<xsd:element name="test1" type="TableType.regression.testxmlschema.test1"/> +

1051

<xsd:element name="test2" type="TableType.regression.testxmlschema.test2"/> +

1052

</xsd:all> +

1053

</xsd:complexType> +

1054

1055

<xsd:element name="testxmlschema" type="SchemaType.regression.testxmlschema"/> +

1056

1057

</xsd:schema>

1058

(1 row)

1059

1060

SELECT schema_to_xml_and_xmlschema('testxmlschema', true, true, 'foo');

1061

schema_to_xml_and_xmlschema

1062

-------------------------------------------------------------------------------------------------------------------

1063

<testxmlschema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="foo" xsi:schemaLocation="foo #"> +

1064

1065

<xsd:schema +

1066

xmlns:xsd="http://www.w3.org/2001/XMLSchema" +

1067

targetNamespace="foo" +

1068

elementFormDefault="qualified"> +

1069

1070

<xsd:simpleType name="INTEGER"> +

1071

<xsd:restriction base="xsd:int"> +

1072

<xsd:maxInclusive value="2147483647"/> +

1073

<xsd:minInclusive value="-2147483648"/> +

1074

</xsd:restriction> +

1075

</xsd:simpleType> +

1076

1077

<xsd:simpleType name="UDT.regression.pg_catalog.text"> +

1078

<xsd:restriction base="xsd:string"> +

1079

</xsd:restriction> +

1080

</xsd:simpleType> +

1081

1082

<xsd:simpleType name="VARCHAR"> +

1083

<xsd:restriction base="xsd:string"> +

1084

</xsd:restriction> +

1085

</xsd:simpleType> +

1086

1087

<xsd:simpleType name="CHAR"> +

1088

<xsd:restriction base="xsd:string"> +

1089

</xsd:restriction> +

1090

</xsd:simpleType> +

1091

1092

<xsd:simpleType name="NUMERIC"> +

1093

</xsd:simpleType> +

1094

1095

<xsd:simpleType name="SMALLINT"> +

1096

<xsd:restriction base="xsd:short"> +

1097

<xsd:maxInclusive value="32767"/> +

1098

<xsd:minInclusive value="-32768"/> +

1099

</xsd:restriction> +

1100

</xsd:simpleType> +

1101

1102

<xsd:simpleType name="BIGINT"> +

1103

<xsd:restriction base="xsd:long"> +

1104

<xsd:maxInclusive value="9223372036854775807"/> +

1105

<xsd:minInclusive value="-9223372036854775808"/> +

1106

</xsd:restriction> +

1107

</xsd:simpleType> +

1108

1109

<xsd:simpleType name="REAL"> +

1110

<xsd:restriction base="xsd:float"></xsd:restriction> +

1111

</xsd:simpleType> +

1112

1113

<xsd:simpleType name="TIME"> +

1114

<xsd:restriction base="xsd:time"> +

1115

<xsd:pattern value="\p{Nd}{2}:\p{Nd}{2}:\p{Nd}{2}(.\p{Nd}+)?"/> +

1116

</xsd:restriction> +

1117

</xsd:simpleType> +

1118

1119

<xsd:simpleType name="TIMESTAMP"> +

1120

<xsd:restriction base="xsd:dateTime"> +

1121

<xsd:pattern value="\p{Nd}{4}-\p{Nd}{2}-\p{Nd}{2}T\p{Nd}{2}:\p{Nd}{2}:\p{Nd}{2}(.\p{Nd}+)?"/> +

1122

</xsd:restriction> +

1123

</xsd:simpleType> +

1124

1125

<xsd:simpleType name="DATE"> +

1126

<xsd:restriction base="xsd:date"> +

1127

<xsd:pattern value="\p{Nd}{4}-\p{Nd}{2}-\p{Nd}{2}"/> +

1128

</xsd:restriction> +

1129

</xsd:simpleType> +

1130

1131

<xsd:complexType mixed="true"> +

1132

<xsd:sequence> +

1133

<xsd:any name="element" minOccurs="0" maxOccurs="unbounded" processContents="skip"/> +

1134

</xsd:sequence> +

1135

</xsd:complexType> +

1136

1137

<xsd:simpleType name="Domain.regression.public.testxmldomain"> +

1138

<xsd:restriction base="VARCHAR"/> +

1139

</xsd:simpleType> +

1140

1141

<xsd:simpleType name="BOOLEAN"> +

1142

<xsd:restriction base="xsd:boolean"></xsd:restriction> +

1143

</xsd:simpleType> +

1144

1145

<xsd:simpleType name="UDT.regression.pg_catalog.bytea"> +

1146

<xsd:restriction base="xsd:base64Binary"> +

1147

</xsd:restriction> +

1148

</xsd:simpleType> +

1149

1150

<xsd:complexType name="SchemaType.regression.testxmlschema"> +

1151

<xsd:sequence> +

1152

<xsd:element name="test1" type="RowType.regression.testxmlschema.test1" minOccurs="0" maxOccurs="unbounded"/>+

1153

<xsd:element name="test2" type="RowType.regression.testxmlschema.test2" minOccurs="0" maxOccurs="unbounded"/>+

1154

</xsd:sequence> +

1155

</xsd:complexType> +

1156

1157

<xsd:element name="testxmlschema" type="SchemaType.regression.testxmlschema"/> +

1158

1159

</xsd:schema> +

1160

1161

<test1> +

1162

1163

<a>1</a> +

1164

one +

1165

</test1> +

1166

1167

<test1> +

1168

1169

<a>2</a> +

1170

two +

1171

</test1> +

1172

1173

<test1> +

1174

1175

<a>-1</a> +

1176

+

1177

</test1> +

1178

1179

1180

<test2> +

1181

1182

<z>55</z> +

1183

<y>abc</y> +

1184

<x>def </x> +

1185

<w>98.60</w> +

1186

<v>2</v> +

1187

999 +

1188

<t>0</t> +

1189

<s>21:07:00</s> +

1190

<r>2009-06-08T21:07:30</r> +

1191

<q>2009-06-08</q> +

1192

+

1193

<o>ABC</o> +

1194

<n>true</n> +

1195

<m>WFla</m> +

1196

</test2> +

1197

1198

1199

</testxmlschema> +

1200

1201

(1 row)

1202

Older »