January 7, 2014 at 1:08 pm
Currently we have an xml stored like this
<FlrBuild xmlns="http://tempuri.org/Floor.xsd">
<DesignStatusFg>3</DesignStatusFg>
</FlrBuild>
When I try and query the xml using the following I can't see the attribute design status fg
DECLARE @xmlComponentItemScrub XML
SELECT @xmlComponentItemScrub = '
<FlrBuild xmlns="http://tempuri.org/Floor.xsd">
<DesignStatusFg>3</DesignStatusFg>
</FlrBuild>'
-- Check if the DesignStatusFg setting already exists
IF EXISTS (SELECT * FROM @xmlComponentItemScrub.nodes('/FlrBuild/DesignStatusFg') n(temp))
BEGIN
Print 'We found it!'
END
IF @xmlComponentItemScrub.exist('(/FlrBuild/DesignStatusFg)[1]')='1'
BEGIN
Print 'We found it again!'
END
When I add xsd to the schema I can then query it and find the attribute I am looking for
DECLARE @xmlComponentItemScrub XML
SELECT @xmlComponentItemScrub = '
<FlrBuild xmlns:xsd="http://tempuri.org/Floor.xsd">
<DesignStatusFg>3</DesignStatusFg>
</FlrBuild>'
-- Check if the DesignStatusFg setting already exists
IF EXISTS (SELECT * FROM @xmlComponentItemScrub.nodes('/FlrBuild/DesignStatusFg') n(temp))
BEGIN
Print 'We found it!'
END
IF @xmlComponentItemScrub.exist('(/FlrBuild/DesignStatusFg)[1]')='1'
BEGIN
Print 'We found it again!'
END
The second query works and finds the attribute using both methods I know of. Why is it that when I add xsd I can then query the attributes? Should I recommend to development that for all xml stored in the DB we should have a standard which requires XSD?
January 7, 2014 at 6:11 pm
When the namespace in the xml is the default namespace as in yours (there is no :prefix in the declaration), then all elements are part of that default namespace and a "WITH XMLNAMESPACES" syntax is required to access the nodes in the xml.
Unfortunately, AFAIK, there is no way to use "WITH XMLNAMESPACES" in an "IF" statement, so you can't access the nodes.
<!-- default namespace declared -->
<FlrBuild xmlns="http://tempuri.org/Floor.xsd">
<DesignStatusFg>3</DesignStatusFg>
</FlrBuild>'
However, by adding a prefix to the namespace, such as "xsd" (it could be "a", "b", "foo", "bar" it doesn't matter) , you take your non-prefixed nodes out of any declared namespace and can now access them without needing to use "WITH XMLNAMESPACE" (as they are not in any declared namespace).
<FlrBuild xmlns:xsd="http://tempuri.org/Floor.xsd">
<DesignStatusFg>3</DesignStatusFg>
</FlrBuild>'
So, as to your question about developers standards, it seems like yes it might be wise not to use a plain "default" namespace if you have the choice, unless you want to work around the problem like this:
DECLARE @xmlComponentItemScrub XML;
SELECT @xmlComponentItemScrub = '
<FlrBuild xmlns="http://tempuri.org/Floor.xsd">
<DesignStatusFg>3</DesignStatusFg>
</FlrBuild>';
-- Check if the DesignStatusFg setting already exists
DECLARE @count int;
WITH XMLNAMESPACES (DEFAULT 'http://tempuri.org/Floor.xsd')
SELECT @count=count(*)
FROM @xmlComponentItemScrub.nodes('/FlrBuild/DesignStatusFg') AS n(temp);
IF @count>0
BEGIN
Print 'We found it!';
END;
WITH XMLNAMESPACES (DEFAULT 'http://tempuri.org/Floor.xsd')
SELECT @count = @xmlComponentItemScrub.exist('(/FlrBuild/DesignStatusFg)[1]');
IF @count = 1
BEGIN
Print 'We found it again!';
END;
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);January 8, 2014 at 8:32 am
In the block above what does the WITH do? Is it creating a work table or something? Is there a cost to having the with statement. The example I have given is a simple one for the post. This table that I am working with has one xml column that stores three different xml schemas for my region. They have about 30 attributes in each. Each region can then put whatever schema they want in that column. I would like it if we can query this column as fields potentially for reporting. I am wondering if the with would cause a loop.
January 8, 2014 at 10:02 am
http://technet.microsoft.com/en-us/library/ms177607.aspx
It is just a declaration of the namespace. Nothing to do with tables, loops or ctes.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);January 8, 2014 at 1:39 pm
I am now running into the next issue. I would like to modify an attribute within the xml. I was looking online and since we are treating this as a table I am not sure what the syntax is for updating the variable
WITH XMLNAMESPACES (DEFAULT 'http://tempuri.org/Floor.xsd')
SET @xmlComponentItemScrub.modify('replace value of (/FlrBuild/DesignStatusFg/text())[1] with "0"')
Can a variable be updated somehow?
January 8, 2014 at 3:53 pm
Yes, but you are going to keep finding these little annoyances working with XML data types in SQL.
I would highly recommend doing this sort of processing in .Net or some other programming language if you can.
That said, here is your "modify" in a working state.
SET @xmlComponentItemScrub.modify('
declare default element namespace "http://tempuri.org/Floor.xsd";
replace value of (/FlrBuild/DesignStatusFg[1]/text())[1] with 0')
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);January 8, 2014 at 4:20 pm
Thanks for the quick reply. I agree XML is difficult to navigate through. I was looking at the loop and time this would take and decided to go another direction. I converted the xml to a string and did a replace. That seems to work significantly faster than variables in this case. I am going to suggest that we change to requiring a namespace for simplicity. Thanks for all the help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply