生成使用SQL Server的各种命名空间的XML文件

huangapple
go评论65阅读模式
英文:

Generate XML files with various namespaces using SQL Server

问题

以下是您要翻译的内容：

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<kmsg xmlns='http://xml.aaa.net/K809' xmlns:env='http://xml.aaa.net/K809/k8msgEnvelope' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xsi:schemaLocation='http://xml.aaa.net/K809 k8Order.xsd'>
	<header>
		<env:envelope>
			<env:source branch='2172' endpoint='' machine='0' password=''/>
			<env:destination branch='2172' endpoint='050107' machine='0'/>
			<env:payload>ORDERRESPONSE</env:payload>
			<env:cfcompany>01</env:cfcompany>
			<env:service>ILDLIVE</env:service>
		</env:envelope>
	</header>
	<body>
		<PurchaseOrder xmlns='urn:schemas-basda-org:2000:purchaseOrder:xdr:3.01'>
			<OrderReferences>
				<CrossReference>ABC 1234567</CrossReference>
			</OrderReferences>
		<Extensions xmlns='http://xml.aaa.net/k8msg/k8OrderExtensions'>
				<Direct>FALSE</Direct>
			</Extensions>
			<Supplier>
				<SupplierReferences>
					<BuyersCodeForSupplier>050107</BuyersCodeForSupplier>
				</SupplierReferences>
			</Supplier>
			<Delivery>
				<PreferredDate>2022-12-24</PreferredDate>
				<SpecialInstructions>Please contact store buyer to confirm quantities and prices, all orders and prices will be final after acceptance of order</SpecialInstructions>
			</Delivery>
			<OrderLine Action='Add' TypeCode='New' TypeDescription='New Item'>
				<Product>
					<SuppliersProductCode>1000486</SuppliersProductCode>
					<BuyersProductCode>1000486</BuyersProductCode>
				</Product>
				<Quantity UOMCode='EA' UOMDescription='Each'>
					<Amount>10</Amount>
				</Quantity>
				<Delivery>
					<PreferredDate>2022-12-24</PreferredDate>
				</Delivery>
			</OrderLine>
		</PurchaseOrder>
	</body>
</kmsg>

请注意，我已经去掉了代码部分，只返回了您要的XML内容的翻译。

英文:

I need to generate XML from SQL Server that the end result looks like this:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<kmsg xmlns='http://xml.aaa.net/K809' xmlns:env='http://xml.aaa.net/K809/k8msgEnvelope' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'  xsi:schemaLocation='http://xml.aaa.net/K809 k8Order.xsd'>
	<header>
		<env:envelope>
			<env:source branch='2172' endpoint='' machine='0' password=''/>
			<env:destination branch='2172' endpoint='050107' machine='0'/>
			<env:payload>ORDERRESPONSE</env:payload>
			<env:cfcompany>01</env:cfcompany>
			<env:service>ILDLIVE</env:service>
		</env:envelope>
	</header>
	<body>
		<PurchaseOrder xmlns='urn:schemas-basda-org:2000:purchaseOrder:xdr:3.01'>
			<OrderReferences>
				<CrossReference>ABC 1234567</CrossReference>
			</OrderReferences>
		<Extensions xmlns='http://xml.aaa.net/k8msg/k8OrderExtensions'>
				<Direct>FALSE</Direct>
			</Extensions>
			<Supplier>
				<SupplierReferences>
					<BuyersCodeForSupplier>050107</BuyersCodeForSupplier>
				</SupplierReferences>
			</Supplier>
			<Delivery>
				<PreferredDate>2022-12-24</PreferredDate>
				<SpecialInstructions>Please contact store buyer to confirm quantities and prices, all orders and prices will be final after acceptance of order</SpecialInstructions>
			</Delivery>
			<OrderLine Action='Add' TypeCode='New' TypeDescription='New Item'>
				<Product>
					<SuppliersProductCode>1000486</SuppliersProductCode>
					<BuyersProductCode>1000486</BuyersProductCode>
				</Product>
				<Quantity UOMCode='EA' UOMDescription='Each'>
					<Amount>10</Amount>
				</Quantity>
				<Delivery>
					<PreferredDate>2022-12-24</PreferredDate>
				</Delivery>
			</OrderLine>
		</PurchaseOrder>
	</body>
</kmsg>

The basic layout has been done but I'm facing trouble with 2 items:

  1. The namespaces at the top. I'm generating the namespaces but it generates out of sync in order of creation and I'm also getting &quot characters. I suspect a characterset mismatch or something similar. How do I get the namespaces to view in the exact order of the document?
  2. How do I add namespaces in the middle of the document? The PurchaseOrder and Extentions tags both have namespaces within them. Can these also be declared at the top of the query, or do I need to add them as static values in SQL?

Here is the code I have currently:

WITH XMLNAMESPACES (
'xsi="http://xml.aaa.net/K809 k8Order.xsd"' AS schemaLocation,
'xsi="http://www.w3.org/2001/XMLSchema-instance"' AS xsi,
'env="http://xml.aaa.net/K809/k8msgEnvelope"' AS env,
'xmlns="http://xml.aaa.net/K809"' AS xmlns1)

SELECT TOP 1
''										AS 'header/env:envelope/env:source/@password',
''										AS 'header/env:envelope/env:source/@machine',
A.SUPPLIER								AS 'header/env:envelope/env:source/@Endpoint',
B.SRCLOC								AS 'header/env:envelope/env:source/@Branch',
'ORDERRESPONSE'							AS 'header/env:envelope/env:payload',
--B.SRCLOC								AS DestinationBranch,
C.FREEATTR3								AS 'header/env:envelope/env:cfCompany',
'ILDLive'								AS 'header/env:envelope/env:service',
'urn:schemas-basda-org:2000:purchaseOrder:xdr:3>' AS 'Body/@PurchaseOrder',
A.SUPPLIER								AS 'Body/PurchaseOrder/Supplier/SupplierReferences/BuyersCodeForSupplier',
DATEDIFF(DAY,'1989/12/31',A.EXPDATEPRD) AS 'Body/PurchaseOrder/Delivery/PreferredDate',	--CONVERT: 2022-12-24
'Test text for Special instructions'	AS 'Body/PurchaseOrder/Delivery/SpecialInstructions',
'New item'								AS 'Body/PurchaseOrder/OrderLine/@TypeDescription',
'New'									AS 'Body/PurchaseOrder/OrderLine/@TypeCode',
'Add'									AS 'Body/PurchaseOrder/OrderLine/@Action',
A.ITEM									AS 'Body/PurchaseOrder/OrderLine/Product/BuyersProductCode',
D.ExternalItemMasterID					AS 'Body/PurchaseOrder/OrderLine/Product/SuppliersProductCode',
F.UnitOfMeasureDesc						AS 'Body/PurchaseOrder/OrderLine/Quantity/@UOMDescription',
E.VolumetricValue						AS 'Body/PurchaseOrder/OrderLine/Quantity/@UOMCode',
CAST(SUM(A.QEDIT) AS INT)				AS 'Body/PurchaseOrder/OrderLine/Quantity/Amount'
--INTO #XMLTemp
FROM TableMain	AS A
	JOIN Table1			AS B ON A.DESTWHS = B.SRCWHS
	JOIN Table2			AS C ON B.SRCLOC = C.LOCATION
	JOIN Table3			AS D ON A.ITEM = D.ItemCode
	JOIN Table4			AS E ON A.ITEM = E.ItemCode
	JOIN Table5			AS F ON F.ItemCode=A.ITEM
WHERE E.Measurement = 'Unit of Buy'
--AND DATEDIFF(DAY,'1989/12/31',A.PRUNDATPRD) = GETDATE()
--ONLY THE LATEST PROPOSALS
GROUP BY B.SRCLOC,C.FREEATTR3,A.SUPPLIER,A.EXPDATEPRD,D.ExternalItemMasterID,A.ITEM,E.VolumetricValue,F.UnitOfMeasureDesc 
FOR XML PATH('kmsg')

and here is the output I have:

<kmsg xmlns:xmlns1="xmlns="http://xml.aaa.net/K809"" xmlns:env="env="http://xml.aaa.net/K809/k8msgEnvelope"" xmlns:xsi="xsi="http://www.w3.org/2001/XMLSchema-instance"" xmlns:schemaLocation="xsi="http://xml.aaa.net/K809 k8Order.xsd"">
  <header>
    <env:envelope>
      <env:source password="" machine="" Endpoint="050354" Branch="1012" />
      <env:payload>ORDERRESPONSE</env:payload>
      <env:cfCompany>01</env:cfCompany>
      <env:service>ILDLive</env:service>
    </env:envelope>
  </header>
  <Body PurchaseOrder="urn:schemas-basda-org:2000:purchaseOrder:xdr:3>">
    <PurchaseOrder>
      <Supplier>
        <SupplierReferences>
          <BuyersCodeForSupplier>050354</BuyersCodeForSupplier>
        </SupplierReferences>
      </Supplier>
      <Delivery>
        <PreferredDate>-20744</PreferredDate>
        <SpecialInstructions>Test text for Special instructions</SpecialInstructions>
      </Delivery>
      <OrderLine TypeDescription="New item" TypeCode="New" Action="Add">
        <Product>
          <BuyersProductCode>1034623</BuyersProductCode>
          <SuppliersProductCode>KHN4.200CX</SuppliersProductCode>
        </Product>
        <Quantity UOMDescription="Each" UOMCode="EA">
          <Amount>2904</Amount>
        </Quantity>
      </OrderLine>
    </PurchaseOrder>
  </Body>
</kmsg>

Any help would be appreciated. I'm not used to working with XML in SQL.

I tried using static namespaces in the SQL part but I don't think that is the way to do it

答案1

得分: 0

以下是代码部分的翻译：

<kmsg xmlns="http://xml.aaa.net/K809"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:env="http://xml.aaa.net/K809/k8msgEnvelope"
    xmlns:po="urn:schemas-basda-org:2000:purchaseOrder:xdr:3.01" xsi:schemaLocation="http://xml.aaa.net/K809 k8Order.xsd">
    <header>
        <env:envelope>
            <env:source password="" machine="" endpoint="050354" branch="2172"/>
            <env:payload>ORDERRESPONSE</env:payload>
            <env:cfcompany>01</env:cfcompany>
            <env:service>ILDLive</env:service>
        </env:envelope>
    </header>
    <body>
        <PurchaseOrder xmlns="urn:schemas-basda-org:2000:purchaseOrder:xdr:3.01">
            <Extensions xmlns="http://xml.aaa.net/k8msg/k8OrderExtensions">
                <Direct>FALSE</Direct>
            </Extensions>
            <Supplier>
                <SupplierReferences>
                    <BuyersCodeForSupplier>050354</BuyersCodeForSupplier>
                </SupplierReferences>
            </Supplier>
            <Delivery>
                <PreferredDate>2022-12-24</PreferredDate>
                <SpecialInstructions>Test text for Special instructions</SpecialInstructions>
            </Delivery>
            <OrderLine TypeDescription="New item" TypeCode="New" Action="Add">
                <Product>
                    <BuyersProductCode>1034623</BuyersProductCode>
                    <SuppliersProductCode>KHN4.200CX</SuppliersProductCode>
                </Product>
                <Quantity UOMDescription="Each" UOMCode="EA">
                    <Amount>10</Amount>
                </Quantity>
            </OrderLine>
        </PurchaseOrder>
    </body>
</kmsg>
英文:

Given some of the errors there are several things you need to keep in mind with XML documents:

  • namespace URIs are case sensitive.
  • element names are case sensitive, so <Body> is a different element than <body>.
  • attribute names case sensitive, so <source Endpoint="..."/> is a different attribute than <source endpoint="..."/>.

WITH XMLNAMESPACES has a particular syntax where 'urn' as prefix defines a namespace URN with a prefix and default 'urn' defines a namespace URN to be used without a prefix, aka. the default namespace.

The following SQL probably isn't correct for your environment but produces XML similar to the output you are looking for - if you update your question to include sample DDL and data this could be tweaked somewhat:

--
-- Setup data...
--
select 'WHS' as DESTWHS, cast('2022-12-24' as date) as EXPDATEPRD, '1034623' as ITEM, 10 as QEDIT, '050354' as SUPPLIER
into dbo.TableMain; --A

select '2172' as SRCLOC, 'WHS' as SRCWHS
into dbo.Table1; --B

select '01' as FREEATTR3, '2172' as [LOCATION]
into dbo.Table2; --C

select 'KHN4.200CX' as ExternalItemMasterID, '1034623' as ItemCode
into dbo.Table3; --D

select '1034623' as ItemCode, 'Unit of Buy' as Measurement, 'EA' as VolumetricValue
into dbo.Table4; --E

select '1034623' as ItemCode, 'Each' as UnitOfMeasureDesc
into dbo.Table5; --F

--
-- XML query...
--
WITH XMLNAMESPACES (
  'urn:schemas-basda-org:2000:purchaseOrder:xdr:3.01' as po,
  'http://xml.aaa.net/K809/k8msgEnvelope' AS env,
  'http://www.w3.org/2001/XMLSchema-instance' as xsi,
  default 'http://xml.aaa.net/K809'
)
SELECT TOP 1
  'http://xml.aaa.net/K809 k8Order.xsd' as [@xsi:schemaLocation],
  B.SRCLOC                                AS [header/env:envelope/env:source/@branch],
  A.SUPPLIER                              AS [header/env:envelope/env:source/@endpoint],
  ''                                      AS [header/env:envelope/env:source/@machine],
  ''                                      AS [header/env:envelope/env:source/@password],
  'ORDERRESPONSE'                         AS [header/env:envelope/env:payload],
  C.FREEATTR3                             AS [header/env:envelope/env:cfcompany],
  'ILDLive'                               AS [header/env:envelope/env:service],
  (
    select
      cast('<Extensions xmlns="http://xml.aaa.net/k8msg/k8OrderExtensions"><Direct>FALSE</Direct></Extensions>' as xml).query('/*'),
      A.SUPPLIER                           AS [po:Supplier/po:SupplierReferences/po:BuyersCodeForSupplier],
      A.EXPDATEPRD                         AS [po:Delivery/po:PreferredDate],
      'Test text for Special instructions' AS [po:Delivery/po:SpecialInstructions],
      'New item'                           AS [po:OrderLine/@TypeDescription],
      'New'                                AS [po:OrderLine/@TypeCode],
      'Add'                                AS [po:OrderLine/@Action],
      A.ITEM                               AS [po:OrderLine/po:Product/po:BuyersProductCode],
      D.ExternalItemMasterID               AS [po:OrderLine/po:Product/po:SuppliersProductCode],
      F.UnitOfMeasureDesc                  AS [po:OrderLine/po:Quantity/@UOMDescription],
      E.VolumetricValue                    AS [po:OrderLine/po:Quantity/@UOMCode],
      CAST(SUM(A.QEDIT) AS INT)            AS [po:OrderLine/po:Quantity/po:Amount]
    for xml path('po:PurchaseOrder'), type
  ).query('
  declare default element namespace "urn:schemas-basda-org:2000:purchaseOrder:xdr:3.01";
  <PurchaseOrder> { /po:PurchaseOrder/* } </PurchaseOrder>') as [body]
FROM TableMain  AS A
    JOIN Table1         AS B ON A.DESTWHS = B.SRCWHS
    JOIN Table2         AS C ON B.SRCLOC = C.LOCATION
    JOIN Table3         AS D ON A.ITEM = D.ItemCode
    JOIN Table4         AS E ON A.ITEM = E.ItemCode
    JOIN Table5         AS F ON F.ItemCode=A.ITEM
WHERE E.Measurement = 'Unit of Buy'
GROUP BY B.SRCLOC,C.FREEATTR3,A.SUPPLIER,A.EXPDATEPRD,D.ExternalItemMasterID,A.ITEM,E.VolumetricValue,F.UnitOfMeasureDesc 
FOR XML PATH('kmsg')

Which yields the XML output:

<kmsg xmlns="http://xml.aaa.net/K809"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:env="http://xml.aaa.net/K809/k8msgEnvelope"
    xmlns:po="urn:schemas-basda-org:2000:purchaseOrder:xdr:3.01" xsi:schemaLocation="http://xml.aaa.net/K809 k8Order.xsd">
    <header>
        <env:envelope>
            <env:source password="" machine="" endpoint="050354" branch="2172"/>
            <env:payload>ORDERRESPONSE</env:payload>
            <env:cfcompany>01</env:cfcompany>
            <env:service>ILDLive</env:service>
        </env:envelope>
    </header>
    <body>
        <PurchaseOrder xmlns="urn:schemas-basda-org:2000:purchaseOrder:xdr:3.01">
            <Extensions xmlns="http://xml.aaa.net/k8msg/k8OrderExtensions">
                <Direct>FALSE</Direct>
            </Extensions>
            <Supplier>
                <SupplierReferences>
                    <BuyersCodeForSupplier>050354</BuyersCodeForSupplier>
                </SupplierReferences>
            </Supplier>
            <Delivery>
                <PreferredDate>2022-12-24</PreferredDate>
                <SpecialInstructions>Test text for Special instructions</SpecialInstructions>
            </Delivery>
            <OrderLine TypeDescription="New item" TypeCode="New" Action="Add">
                <Product>
                    <BuyersProductCode>1034623</BuyersProductCode>
                    <SuppliersProductCode>KHN4.200CX</SuppliersProductCode>
                </Product>
                <Quantity UOMDescription="Each" UOMCode="EA">
                    <Amount>10</Amount>
                </Quantity>
            </OrderLine>
        </PurchaseOrder>
    </body>
</kmsg>

huangapple
  • 本文由 发表于 2023年2月27日 17:40:28
  • 转载请务必保留本文链接：https://go.coder-hub.com/75578818.html
  • sql-server
  • t-sql
  • xml
  • xml-namespaces
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定