XQuery SQL XML values

Question

我们必须找到一个查询解析XML来得到这样的结果:

COL_ACTION N           COL_NAME_I       COL_VALUE_AFTER
---------- ----------- ---------------- -------------------
INS        1           N                1
INS        1           TST_ID           28
INS        1           TST_DATA         data2
INS        2           N                2
INS        2           TST_ID           27
INS        2           TST_DATA         data1

第N列的值取决于第一行的值。(COL_NAME_I = N)的XML数据集

XML包含:

DECLARE @XML XML =
N'
<DATASET>
<XML_INS>
<IROW xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<N>1</N>
<TST_ID>28</TST_ID>
<TST_DATA>data2</TST_DATA>
</IROW>
<IROW xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<N>2</N>
<TST_ID>27</TST_ID>
<TST_DATA>data1</TST_DATA>
</IROW>
</XML_INS>
</DATASET>
';

我能做的查询是:

SELECT RIGHT(ca.value('local-name(.)','CHAR(7)'), 3) AS COL_ACTION,
x.value('(//N)[1]', 'int') AS N,
--       cn.value('./text()[1]','int') AS NI,
ci.value('local-name(.)','sysname') AS COL_NAME_I,
ci.value('./text()[1]','nvarchar(max)') AS COL_VALUE_AFTER 
FROM   @XML.nodes('.') AS T(x)
OUTER APPLY @XML.nodes('DATASET/*') AS TA(ca)
OUTER APPLY @XML.nodes('DATASET/XML_INS/IROW/N/*') AS TN(cn)
OUTER APPLY @XML.nodes('DATASET/XML_INS/IROW/*') AS TI(ci);

问题仍然在"N"没有期望值的列:

COL_ACTION N           COL_NAME_I       COL_VALUE_AFTER
---------- ----------- ---------------- -------------------
INS        1           N                1
INS        1           TST_ID           28
INS        1           TST_DATA         data2
INS        1           N                2
INS        1           TST_ID           27
INS        1           TST_DATA         data1

我尝试了不同的方法，如在SQL注释中看到的，但没有产生好的结果…

Answer 1

您需要每个.nodes引用前一个，以便只带回它的子节点。否则，您将获得整个文档的每个后代节点。

SELECT RIGHT(action.value('local-name(.)','CHAR(7)'), 3) AS COL_ACTION,
irow.value('(N/text())[1]', 'int') AS N,
ci.value('local-name(.)','sysname') AS COL_NAME_I,
ci.value('text()[1]','nvarchar(max)') AS COL_VALUE_AFTER 
FROM   @XML.nodes('DATASET/*') AS x1(action)
OUTER APPLY x1.action.nodes('IROW') AS x2(irow)
OUTER APPLY x2.irow.nodes('*') AS TI(ci);

,db&lt的在小提琴

Answer 2

应该这样做:

SELECT
irow.value('local-name(..)', 'NVARCHAR(100)') AS COL_ACTION,
irow.value('(./N)[1]', 'NVARCHAR(100)') AS N,
child.value('local-name(.)', 'NVARCHAR(100)') AS COL_NAME_I,
child.value('.', 'NVARCHAR(100)') AS COL_VALUE_AFTER
FROM @XML.nodes('/DATASET/*/IROW') AS n1(irow)
CROSS APPLY irow.nodes('./*') AS n2(child)

Answer 3

我是一个新贡献者，如果我错了，很抱歉。

我在工作中使用了这种方法:Link

我的示例工作代码:

CREATE TABLE #FieldPermissionsTable
( 
TE_ID VARCHAR(MAX),
Value VARCHAR(MAX),
Value2 VARCHAR(MAX),
NAVI_USER VARCHAR(MAX)
) 
--SET @FieldPermissionAccessXML = '<Row_ID><Elements ID="1" Value="1" Value2="NULLnROLLA" Navi_User="testuser" /><Elements ID="3" Value="tespit" Value2="NULLnROLLA" Navi_User="testuser" /><Elements ID="6" Value="aciklama" Value2="NULLnROLLA" Navi_User="testuser" /></Row_ID>'
-- SELECT @FieldPermissionAccessXML = [dbo].[to_xml_replace] (@FieldPermissionAccessXML)
EXEC sp_xml_preparedocument @XmlHandle output,@FieldPermissionAccessXML
--'<FieldPermissions>
--<FieldPermission FieldName="" RoleID="1" Access="1" />
--<FieldPermission FieldName="" RoleID="2" Access="1" />'
--select * from JMP_FieldPermissions
INSERT INTO #FieldPermissionsTable 
SELECT *--ID,Value, Value2, Navi_User 
FROM  OPENXML (@XmlHandle, './Row_ID/Elements') 
WITH (TE_ID VARCHAR(MAX) '@ID',
Value VARCHAR(MAX) '@Value',
Value2 VARCHAR(MAX) '@Value2',
NAVI_USER VARCHAR(MAX) '@Navi_User'

)